[GENERAL] tsearch2 index missing
I'm using tsearch 2 - added a column named vectors to a table, updated it according to the documentation, created an index. So far it's all worked fine. Problem is I decided to include a new column in the contents of vectors, so I added the column, deleted my index, re-updated the vectors column and added a trigger to update the vectors. Now when I try to update I get an error message saying "tsvector column "contact_ft_index" does not exist" What I don't understand is that the contact_ft_index it's talking about was never a column, it was the name of the previous index. Am I missing something here? How do I recover this? Toby --- Life is poetry, write it in your own words --- Toby Tremayne Senior Technical Consultant Lyricist Software 0416 048 090 ICQ: 13107913
Re: [GENERAL] Addons
David Legault wrote: > I can't seem to be able to change/add builtin contrib items using the > installer after it's been installed already. Is there another way to access > those modules and install them manually ? There will be installer scripts in $PGDATA/share/ It sounds like you are running Windows so you would be do something like: psql -U postgres database < C:\"Program Files\PostgreSQL\share/contrib/... Joshua D. Drake > > Thanks > > On 2/17/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: >> >> David Legault wrote: >> > Hello, >> > >> > I can't find a list of addons on the website. I'd like to view the list >> of >> > addons like pgcrypto and download/install some of them into my >> installation >> > so I can use some of the functions. >> >> There is www.pgfoundry.org and there is the built in contrib. The built >> in contrib has the items such as pgcrypto. >> >> > >> > And is there any advanced docs on the PL/PGSQL language like how to >> > manipulate strings (string replace, search, split, etc) >> >> The language doesn't :), PostgreSQL does. Unlike something like Perl (or >> plPerl), something like replace() uses the PostgreSQL function replace() >> not a language function. See here: >> >> http://www.postgresql.org/docs/8.2/static/functions.html >> >> You can call anything in the above link from plpgsql, directly. >> >> Sincerely, >> >> Joshua D. Drake >> >> >> > >> > Thanks >> > >> > David >> > >> >> >> -- >> >> === 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/ >> >> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate >> PostgreSQL Replication: http://www.commandprompt.com/products/ >> >> > -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Addons
I can't seem to be able to change/add builtin contrib items using the installer after it's been installed already. Is there another way to access those modules and install them manually ? Thanks On 2/17/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: David Legault wrote: > Hello, > > I can't find a list of addons on the website. I'd like to view the list of > addons like pgcrypto and download/install some of them into my installation > so I can use some of the functions. There is www.pgfoundry.org and there is the built in contrib. The built in contrib has the items such as pgcrypto. > > And is there any advanced docs on the PL/PGSQL language like how to > manipulate strings (string replace, search, split, etc) The language doesn't :), PostgreSQL does. Unlike something like Perl (or plPerl), something like replace() uses the PostgreSQL function replace() not a language function. See here: http://www.postgresql.org/docs/8.2/static/functions.html You can call anything in the above link from plpgsql, directly. Sincerely, Joshua D. Drake > > Thanks > > David > -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Re: [GENERAL] Problem with index not always being used
Thanks for the thoughts, certainly I will look into what you have explained. However, the behavior that you expressed isn't what is occuring. In the 12, 16 example 12 does have more rows than 16. However, there are many cases when this isn't true, that is other states have more rows than 12 and the optomizer does use the index when I query them. There are 6 states total that the optomizer doesn't use the index. The other 5 states would rank row rise in the minority (of number rows) which would make me believe the optomizer would want to use the index. That said I am investigating the statistics and the random_page_cost. Thank you for your insight. Rob. On 2/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Rob Tester" <[EMAIL PROTECTED]> writes: > SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table > where > SELECT * FROM STUFF WHERE state=16 --Uses the index. This behavior is intended and appropriate, if there are lots of rows with state=12 and not many with state=16. As an example, if nearly the whole table had state=12 you would certainly not wish it to use an indexscan for that. The correct way to think about your gripe is that the planner's cutting over at the wrong row density. There are a couple of places to look for a solution: First, are the planner's estimated row counts for both cases reasonably close to reality, according to EXPLAIN ANALYZE? If not, you may need to increase the statistics target (either globally with default_statistics_target or for the state column with ALTER TABLE). Don't forget to re-ANALYZE the table after changing the target. If the statistics are good then you need to fool with the planner's cost parameters to get it to make decisions that reflect your environment. Decreasing random_page_cost is usually the thing to do if it's choosing seqscans too readily. But be wary of choosing a new value on the basis of just one test case. You can find a lot about this in the pgsql-performance list archives, and there are several relevant articles at techdocs: http://www.postgresql.org/docs/techdocs regards, tom lane
[GENERAL] CodeGear working on a new DB access layer for Delphi, but.....
It seems they are not going to include support for PostgreSQL (just the "big" 4), which would be a big mistake if you ask me. Here is the link to the lead developer's blog who is working on this new technology http://blogs.codegear.com/SteveShaughnessy/archive/2007/02/16/31865.aspx?Pending=true Take a peek and add a comment (if you want to) that nicely suggests PostgreSQL support. I know there are not a lot of Delphi developers on this list, but the more stuff that supports PostgreSQL the better right? Later -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] user input during runtime
Bruce Momjian wrote: > Magnus Hagander wrote: >> Win32 will deal with the backticks Ok, but not the read && echo part. >> You can set it to the output of a variable, for example >> \set x `echo foo` >> >> but I haven't been able to trick it into actually reading something. One >> would think something like: >> \set x `set /p Z= && echo %Z%` >> would work, but it doesn't. One reason for this is that %Z% is actually >> resolved at parsing time. it's a pain in CMD language, but that's how it >> works. :-( > > CMD "language"? LOL > We have our own language? ;) Joshua D. Drake -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] user input during runtime
Magnus Hagander wrote: > Win32 will deal with the backticks Ok, but not the read && echo part. > You can set it to the output of a variable, for example > \set x `echo foo` > > but I haven't been able to trick it into actually reading something. One > would think something like: > \set x `set /p Z= && echo %Z%` > would work, but it doesn't. One reason for this is that %Z% is actually > resolved at parsing time. it's a pain in CMD language, but that's how it > works. :-( CMD "language"? LOL -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Small request re error message
Scott Ribe wrote: > > Oh. Yea, I can see that, but even if the endian-ness is the same, it > > still might not work. Even a different compiler flag will cause a > > failure to run properly. > > Sure. You can't flag every possible error. But my Intel & PPC Macs look > identical, and I compile with identical flags. So it would help people like > me, all one or two of us ;-) What would make more sense than printing the hex is to print a specific message if the endian-ness doesn't match, but I am worried people might assume it will work when the endian does match. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] User-interfaces with transaction support
Hello, I'm looking for a UI concept for my model-driven development tool which works with Python and PostgreSQL, and a thin ORM layer between. On the application and database layer it supports transactions (just wrapped the PostgreSQL features). Now I want to add also UI support to the prototype and this usability questions are that what makes my head screwed up. I guess that some of you also build user-interfaces for PostgreSQL in the one ore other project? Thus you probably already have experience how to integrate transaction support into the UI (a locally installed, native GUI, or a Web application). My considerations are less in a technical meaning, but in an usability meaning. Example: You have a Foo class (or table) with the attribute (column) name. You want the end-user to be able to edit any Foo object (or record). So there is some "Edit Foo" dialog with a text entry widget for the name, a "Save" button, and a "Cancel" button, yes? It is obvious for the end-user what will happen if he clicks on Save or on Cancel. And of course it would be possible to have the UI using just one transaction for selecting this record and updating it in the database. Deleting a single record would be another simple example for a transaction with just a single call to the database. But what about transactions spanning multipe calls on a single object, calls on multiple objects (including creating or linking them), or even on objects of multiple components (databases)? How is it transparent for an end-user which of the activities made in the UI are in a single transaction or in different ones? Do you put anything inside a transaction into a single dialog? Or do you use tabs/notebooks? I have put some more background here http://openswarm.blogspot.com/2007/02/transactions-in-user-interfaces.html but I hope that you already got a picture what makes me thinking and you probably want to share the one or other idea for the UI concept. I'm too curious how you managed this issue in your projects. Best regards, Anastasios ---(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] Problem with index not always being used
"Rob Tester" <[EMAIL PROTECTED]> writes: > SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table > where > SELECT * FROM STUFF WHERE state=16 --Uses the index. This behavior is intended and appropriate, if there are lots of rows with state=12 and not many with state=16. As an example, if nearly the whole table had state=12 you would certainly not wish it to use an indexscan for that. The correct way to think about your gripe is that the planner's cutting over at the wrong row density. There are a couple of places to look for a solution: First, are the planner's estimated row counts for both cases reasonably close to reality, according to EXPLAIN ANALYZE? If not, you may need to increase the statistics target (either globally with default_statistics_target or for the state column with ALTER TABLE). Don't forget to re-ANALYZE the table after changing the target. If the statistics are good then you need to fool with the planner's cost parameters to get it to make decisions that reflect your environment. Decreasing random_page_cost is usually the thing to do if it's choosing seqscans too readily. But be wary of choosing a new value on the basis of just one test case. You can find a lot about this in the pgsql-performance list archives, and there are several relevant articles at techdocs: http://www.postgresql.org/docs/techdocs 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] requests / suggestions to help with backups
On Thu, Feb 15, 2007 at 22:39:13 -0500, Lou Duchez <[EMAIL PROTECTED]> wrote: > > 1) "grant select on database ..." or, hypothetically, "grant select on > cluster". The goal would be to create a read-only PostgreSQL user, one > who can read the contents of an entire database (or even the entire > cluster) but make no changes. Currently, to do my cron job, I have to > specify a "trusted" user, otherwise PostgreSQL will ask for a password; > it sure would be nice if I could neuter my "trusted" user so he cannot > do any damage. (Yes, I could set read-only privileges on a table-by-table > basis. Obviously, that's a pain.) You can use ident authentication instead of trust. That may make using the postgres db account for the cronjob's connection an acceptible risk. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Help with an index and the optimizer
I am using pg 8.1.4, I have a table with 1.1 million rows of data (see below for table definition). One field state is numeric and has an index. The index is not always picked up when searching the table by state only and I can't figure out why. So: SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table where SELECT * FROM STUFF WHERE state=16 --Uses the index. I have run Analyze on the table as well as vacuumed it and reindexed it. At first I thought it might be a type mismatch but forcing the number to numeric (i.e cast(12 as numeric(2,0)) doesn't change the behavior. However setting the enable_seqscan=off does force both queries to use the index. Using the index in all cases is faster than a seq scan according to explain analyze. Any thoughts on how to get the optimizer to pick up the index at all times? I am desperate for fresh ideas. Thanks, Rob. Table/index definitions: CREATE TABLE stuff( id serial NOT NULL, module character(8), tlid numeric(10), dirp character(2), name character(30), type character(4), dirs character(2), zip numeric(5), state numeric(2), county numeric(3), CONSTRAINT stuff_pk PRIMARY KEY (id), ) WITHOUT OIDS; CREATE INDEX ndx_cc_state ON stuff USING btree (state);
Re: [GENERAL] Problem with index not always being used
I am using pg 8.1.4, I have a table with 1.1 million rows of data (see below for table definition). One field state is numeric and has an index. The index is not always picked up when searching the table by state only and I can't figure out why. So: SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table where SELECT * FROM STUFF WHERE state=16 --Uses the index. I have run Analyze on the table as well as vacuumed it and reindexed it. At first I thought it might be a type mismatch but forcing the number to numeric (i.e cast(12 as numeric(2,0)) doesn't change the behavior. However setting the enable_seqscan=off does force both queries to use the index. Using the index in all cases is faster than a seq scan according to explain analyze. Any thoughts on how to get the optimizer to pick up the index at all times? I am desperate for fresh ideas. Thanks, Rob. Table/index definitions: CREATE TABLE stuff( id serial NOT NULL, module character(8), tlid numeric(10), dirp character(2), name character(30), type character(4), dirs character(2), zip numeric(5), state numeric(2), county numeric(3), CONSTRAINT stuff_pk PRIMARY KEY (id), ) WITHOUT OIDS; CREATE INDEX ndx_cc_state ON stuff USING btree (state);
Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
On Sat, Feb 17, 2007 at 03:15:25PM +0100, Karsten Hilbert wrote:
> On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote:
> > But if we insert a set schema search_path command in an SQL function,
> > the caller will be affected by it. Doing reset search_path before
> > returning to caller might solve some of problems, but it will not
> > recover caller's special search_path. How do you solve the problem?
>
> Schema-qualifying object accesses would be tedious,
> omission-prone but not liable to the above problem.
If you schema-qualify objects instead of setting search_path then
don't forget about operators. A query like
SELECT col
FROM schemaname.tablename
WHERE othercol = schemaname.funcname(someval)
is vulnerable because the caller might have defined an = operator
for the appropriate data types and set search_path to find it before
the one in pg_catalog. To be safe you'd need to use
SELECT col
FROM schemaname.tablename
WHERE othercol operator(pg_catalog.=) schemaname.funcname(someval)
which is harder to read and, as Karsten mentioned, prone to omission.
Also, this query might still be vulnerable if funcname() isn't
carefully written.
A PL/pgSQL function could save and restore the caller's search_path
with something like
oldpath := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', oldpath, false);
If the function raises an exception then search_path wouldn't be
reset unless you catch exceptions and reset the path in the
exception-handling code.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How would you handle updating an item and related stuff all at once?
Rick Schumeyer wrote: Completely off topic, (but not worth a separate post) I have been forced to use a little bit of mysql lately...did you know that if you use transaction and foreign key syntax with myisam tables, it does not complain...it just silently ignores your requests for transactions and foreign key checks. Yikes! I had incorrectly assumed I would get an error message indicating that transactions are not supported. Oh well. I ran into the same thing. Actually it may have been that a dump, restore caused tables to be created with myisam engine instead of innodb. Regardless, I lost faith in MySQL except for things it good at... fast read only web database. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Addons
David Legault wrote: > Hello, > > I can't find a list of addons on the website. I'd like to view the list of > addons like pgcrypto and download/install some of them into my installation > so I can use some of the functions. There is www.pgfoundry.org and there is the built in contrib. The built in contrib has the items such as pgcrypto. > > And is there any advanced docs on the PL/PGSQL language like how to > manipulate strings (string replace, search, split, etc) The language doesn't :), PostgreSQL does. Unlike something like Perl (or plPerl), something like replace() uses the PostgreSQL function replace() not a language function. See here: http://www.postgresql.org/docs/8.2/static/functions.html You can call anything in the above link from plpgsql, directly. Sincerely, Joshua D. Drake > > Thanks > > David > -- === 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/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How do I use returning in a view?
"Karen Hill" <[EMAIL PROTECTED]> writes: > I have an updateable view (using rules) that I'm trying to improve by > using 8.2's RETURNING feature to place the result of one insert into > the next. That's not what it's for. RETURNING in an insert rule is to define what to return if someone does an INSERT/RETURNING *on the view*. It's thrown away if the rule is invoked by a plain INSERT. 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] user input during runtime
David Fetter wrote: > On Fri, Feb 16, 2007 at 08:23:48PM -0500, Bruce Momjian wrote: >> Ashish Karalkar wrote: >>> Hello All, >>> I want to prompt user to input some value and do some action on that value >>> in runtime of a sql script. >>> Is there any psql command to do this ?? >>> I can use \echo do display massage >>> but to take input what is the command? >>> Thanks in advance >> You can do: >> >> \echo -n 'Enter value: ' >> \set x `read && echo $REPLY` > > Neat trick! Is there one that works on all our supported platforms? > I'm guessing Win32 will just get confused about backticks. Win32 will deal with the backticks Ok, but not the read && echo part. You can set it to the output of a variable, for example \set x `echo foo` but I haven't been able to trick it into actually reading something. One would think something like: \set x `set /p Z= && echo %Z%` would work, but it doesn't. One reason for this is that %Z% is actually resolved at parsing time. it's a pain in CMD language, but that's how it works. :-( //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] user input during runtime
On Fri, Feb 16, 2007 at 08:23:48PM -0500, Bruce Momjian wrote: > Ashish Karalkar wrote: > > Hello All, > > I want to prompt user to input some value and do some action on that value > > in runtime of a sql script. > > Is there any psql command to do this ?? > > I can use \echo do display massage > > but to take input what is the command? > > Thanks in advance > > You can do: > > \echo -n 'Enter value: ' > \set x `read && echo $REPLY` Neat trick! Is there one that works on all our supported platforms? I'm guessing Win32 will just get confused about backticks. > in psql. I think we will have \prompt in 8.3. Well, I guess it's not all that crucial with \prompt coming out. :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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] Addons
Hello, I can't find a list of addons on the website. I'd like to view the list of addons like pgcrypto and download/install some of them into my installation so I can use some of the functions. And is there any advanced docs on the PL/PGSQL language like how to manipulate strings (string replace, search, split, etc) Thanks David
Re: [GENERAL] Anticipatory privileges
Tom Lane wrote: How dangerous is it to UPDATE pg_class directly, perhaps copying the relacl column for a table that I've done by hand with GRANT. You can do it, and it will seem to work. However, unless you also make entries in pg_shdepend, bad things will happen if you later drop any of the users mentioned in the ACL. Your code will also be vulnerable to breakage in future releases if we change any of these details. A better approach is to write a plpgsql function that assembles and EXECUTEs the required GRANT commands. Okay, thanks - guess it's time to learn some real plpgsql control structures. - John Burger MITRE ---(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] Small request re error message
> Oh. Yea, I can see that, but even if the endian-ness is the same, it > still might not work. Even a different compiler flag will cause a > failure to run properly. Sure. You can't flag every possible error. But my Intel & PPC Macs look identical, and I compile with identical flags. So it would help people like me, all one or two of us ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] How do I use returning in a view?
CREATE RULE ins_productionlog AS ON INSERT TO vwProductionlog DO INSTEAD ( INSERT INTO PRODUCTIONLOG (machine_name,product_serial_id,production_time,product_number,id) VALUES (new.machine_name, new.product_serial_id, new.production_time,new.product_number, DEFAULT) RETURNING productionlog.machine_name, productionlog.product_serial_id, productionlog.production_time, productionlog.product_number, productionlog.id AS foreign_id; INSERT INTO TTEST (name, id) VALUES (new.name, vwProductionlog.foreign_id ) ; ); I have an updateable view (using rules) that I'm trying to improve by using 8.2's RETURNING feature to place the result of one insert into the next. I want to be able to put the returning "productionlog.id AS foreign_id" into table TTEST. Is that even possible just using RULES? If it is, what would be the correct syntax? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Anticipatory privileges
"John D. Burger" <[EMAIL PROTECTED]> writes: > How dangerous is it to UPDATE pg_class > directly, perhaps copying the relacl column for a table that I've > done by hand with GRANT. You can do it, and it will seem to work. However, unless you also make entries in pg_shdepend, bad things will happen if you later drop any of the users mentioned in the ACL. Your code will also be vulnerable to breakage in future releases if we change any of these details. A better approach is to write a plpgsql function that assembles and EXECUTEs the required GRANT commands. 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] Anticipatory privileges
Alvaro Herrera wrote: If I am reading the (7.4) docs correctly, privileges can be granted only with respect to tables that exist at the time the GRANT command is given Yes. In fact, I have to individually grant access to each table, and any associated sequences, yes? How dangerous is it to UPDATE pg_class directly, perhaps copying the relacl column for a table that I've done by hand with GRANT. I'm thinking something like this: => grant all on annotations to public; => update pg_class set relacl = (select relacl from pg_class where relname = 'annotations') where relnamespace = (select oid from pg_namespace where nspname = 'public'); This will "grant" access to indexes and other stuff that may be unnecessary, but is this a sound approach? (By the way, are there in fact any other kinds of objects that I may need to allow access to, other than tables and sequences?) Another solution to my access control issues is to change the owner of the tables and sequences. Can I safely do this with an UPDATE on pg_class? Thanks, and sorry if these are dumb questions, but I haven't been able to glean the answers directly from the docs. - John Burger MITRE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Cast record as text SOLVED
Mikko Partio wrote: I agree that the ability to restore changes is quite nice, but my primary goal is to record changes from many tables into one table, and I think tablelog does not offer that. Do you know any way of casting a record to text, or perhaps a different way altogether to audit to one table? It's hard to believe I am the first person to come up to this problem. Regards MP Got it solved with pl/perl, guess pl/pgsql was the wrong choice of language for a dynamic thing such as this. Regards MP ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_tablespace.spcacl
Alexi Gen wrote: > Hello, > > pg_tablespace contains information about all the tablespaces available on > the system. > The [spcacl] column for a particular record - contains a string value of > the names of users that have permissions on the tablespace. > I'm looking for any info as to why this approach was taken? > Can someone point me to a page / document? Because it's the same approach used everywhere else? The underlying reason is that it's more efficient than using a normalized approach. Of course, internally they aren't strings, but arrays of ACL items, which are in turn tuples of (grantor ID, grantee ID, with_grant_option, privileges), stored as three 32-bit ints. They are converted in string format only for display, just like everything else. Does that answer your question? If it doesn't, please be more specific. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] [ANNOUNCE] Advisory on possibly insecure security definer functions
On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote: > But if we insert a set schema search_path command in an SQL function, > the caller will be affected by it. Doing reset search_path before > returning to caller might solve some of problems, but it will not > recover caller's special search_path. How do you solve the problem? Schema-qualifying object accesses would be tedious, omission-prone but not liable to the above problem. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pg_tablespace.spcacl
Hello, pg_tablespace contains information about all the tablespaces available on the system. The [spcacl] column for a particular record - contains a string value of the names of users that have permissions on the tablespace. I'm looking for any info as to why this approach was taken? Can someone point me to a page / document? Cheers _ Get up-to-date with movies, music and TV. Its happening on MSN Entertainment http://content.msn.co.in/Entertainment/Default ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cast record as text
A. Kretschmer wrote: My original idea was to log changes from different tables to one audit table, and I think tablelog uses separate audit tables for each monitored table? Yes, but with tablelog it is possible to restore any changes, you can restore a table. A blog-entry from Andreas Scherbaum, the maintainer, about tablelog: http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html I agree that the ability to restore changes is quite nice, but my primary goal is to record changes from many tables into one table, and I think tablelog does not offer that. Do you know any way of casting a record to text, or perhaps a different way altogether to audit to one table? It's hard to believe I am the first person to come up to this problem. Regards MP ---(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] Problem writing sql statement....
These seems to work, thx... :) BTJ On Fri, 16 Feb 2007 09:23:44 -0600 "Adam Rich" <[EMAIL PROTECTED]> wrote: > > Or, if you need the whole row: > > SELECT at1.* FROM a_table as at1 > WHERE EXISTS ( > SELECT 1 FROM a_table as at2 > WHERE at2.my_date = at1.my_date > AND at2.prod_id = at1.prod_id > AND at2.primary_key <> at1.primary_key > ) > > This form can easily be adjusted to show > only certain duplicates, or only to delete > certain duplicates. > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson > Sent: Friday, February 16, 2007 9:13 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Problem writing sql statement > > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 02/16/07 01:44, Bjørn T Johansen wrote: > > Not exactly what I want... I don't know the date or id, I just > > need to find all rows that have the same date and the same id.. > > SELECT SOME_DATE, PRODUCTIONID, COUNT(*) > FROM A_TABLE > GROUP BY SOME_DATE, PRODUCTIONID > HAVING COUNT(*) > 1; > > > > > > BTJ > > > > On Thu, 15 Feb 2007 16:46:21 -0600 > > Ron Johnson <[EMAIL PROTECTED]> wrote: > > > > On 02/15/07 15:13, Bjørn T Johansen wrote: > I have a table that I want to find rows that have the same value > in two fields, e.g. all rows that have the same date and also the > same productionid... How do I write such an sql statement? > > If I understand your question: > > > > SELECT FIELD_1, FIELD_2, COUNT(*) > > FROM A_TABLE > > WHERE SOME_DATE = '-mm-dd' > > AND PRODUCTIONID = > > GROUP BY FIELD_1, FIELD_2 > > HAVING COUNT(*) > 1; > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFF1coSS9HxQb37XmcRAlj5AJ94KSt0BCWwFehMNha4Ljf/Cr0tDQCg6AZN > JF4XWsS68ru0jsNaQjvHo20= > =AKKx > -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 > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
