Re: [GENERAL] Connection & logging Problems
Ralph Smith <[EMAIL PROTECTED]> writes: > When I: > [EMAIL PROTECTED]:/usr/lib/postgresql/7.4/bin$ psql -U > airburst airburst -p 5433 > I get: > psql: FATAL: IDENT authentication failed for user "airburst" This is not surprising, seeing that you're evidently logged in as postgres not airburst. psql's -U option is basically guaranteed not to work under IDENT authentication: you have to be logged in as the same username, so -U is useless. If that's not what you want, you need to change the pg_hba.conf file --- see http://www.postgresql.org/docs/7.4/static/client-authentication.html On a single-user box it wouldn't be unreasonable to use TRUST auth (at least for local connections); otherwise you probably want to think about setting up passwords. > My postgresql.conf has: > syslog = 2 # 0 # range 0-2; 0=stdout; 1=both; > 2=syslog > client_min_messages = debug1 > log_min_messages = debug1 > log_min_error_statement = error > YET I'm getting NO logging in either /var/log/syslog nor in /var/log/ > postgresql/postgresql-7.4-main.log I think Postgres is probably faithfully sending messages to the syslog daemon, and the syslog daemon is throwing 'em away because it's not configured to log 'em. Check your local documentation for syslogd, but you probably need something like local0.*/var/log/postgresql added to its configuration file. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] uniquely indexing Celko's nested set model
On Oct 19, 2007, at 16:42 , Richard Broersma Jr wrote: Is it possible to constraint both the LEFT and RIGHT fields of a record to use the same index? I am looking for a way to ensure for all LEFTs and RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to same value. You can define a check constraint to handle this: CREATE OR REPLACE FUNCTION strict_nested_set_node_check(INTEGER, INTEGER) RETURNS BOOLEAN STRICT IMMUTABLE SECURITY DEFINER LANGUAGE SQL AS $_$ SELECT ((abs($1) < abs($2)) AND ($2 - $1 - 1) % 2 = 0) $_$; COMMENT ON FUNCTION strict_nested_set_node_check(INTEGER, INTEGER) IS 'Convenience function to encapsulate the check conditions for the lower and ' ' upper bounds (often called ''left'' and ''right'') for strict nested set ' 'implementations.'; CREATE TABLE nodes ( node_id SERIAL PRIMARY KEY , node_lower INTEGER NOT NULL , node_upper INTEGER NOT NULL , UNIQUE (query_plan_id, node_lower) , UNIQUE (query_plan_id, node_upper) , CHECK (strict_nested_set_node_check(node_lower, node_upper)) ); To actually guarantee that each lower and upper value is only used once, I think you'd need to write a trigger that checks that each value is only used once. I haven't used such trigger when I've used nested sets, however. If you handle your table modifications through functions and test your functions thoroughly, you can be pretty sure that your table updates aren't going to cause any duplication of this time. Then again, maybe I should add the trigger to be on the safe side :) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] uniquely indexing Celko's nested set model
--- On Fri, 10/19/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > Is it possible to constraint both the LEFT and RIGHT > > fields of a record to use the same index? I am looking for > > a way to ensure for all LEFTs and RIGHTs in a table, that is > > it is impossible for any LEFT or RIGHT to have to same > > value. > a check constraint ought to do it True, but how do I insure that one record's left does not equal another record's right? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] uniquely indexing Celko's nested set model
On Oct 19, 2007, at 7:37 PM, Scott Marlowe wrote:
On 10/19/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
Is it possible to constraint both the LEFT and RIGHT fields of a
record to use the same index? I am looking for a way to ensure
for all LEFTs and RIGHTs in a table, that is it is impossible for
any LEFT or RIGHT to have to same value.
a check constraint ought to do it
check (field1<>field2)
That won't catch {1,2} {3,1}.
I don't think there's any way to have an index cover two fields in
that way. The only way I can see to do it with an index would be to
have each row of the OPs mental model to map onto two rows of the
table, along with a boolean saying whether the value was for a "left"
or a "right".
There's probably a much, much more elegant way to do it, but this
might work in an existence proof sort of way:
create table moststuff {
id integer primary key,
whatever text
};
create table leftright {
a integer primary key,
b integer references moststuff(id),
lr text unique,
constraint foo check (b = abs(a))
};
Cheers,
Steve
---(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] uniquely indexing Celko's nested set model
On 10/19/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > Is it possible to constraint both the LEFT and RIGHT fields of a record to > use the same index? I am looking for a way to ensure for all LEFTs and > RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to > same value. a check constraint ought to do it check (field1<>field2) ---(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] ERROR: Could not access status of transaction ####
On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > > On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > >> Thank you Scott! > >> > >> I'm away from my desk and will dive back into it. > >> Fortunately I have two machines, each w/ 7.4 & 8.2 on them. New > >> installs on Ubuntu 7.4. > >> > >> As to why I had 'no role or database' errors yesterday, am I right > >> that it was either: > >> A) I accidentally did a pg_dump when I thought I'd done a > >> pg_dumpall, or > >> B) Using the text file output of pg_dumpall behaves differently on > >> import than the -Fc format? > >> I'd imported it w/ psql, since it was a text file. > == > > On Oct 19, 2007, at 2:06 PM, Scott Marlowe wrote: > > > Most likely the first. Without a pg_dumpall you don't get the > > accounts. Note that you can do just a pg_dumpalll -g to get the > > "global" data, which includes the accounts. > -- > I'm making progess, but SLOWLY... > > I now have a new db postgres, and now my targed db airburst > > I have both 7.4 & 8.2 on this box. > When I: > [EMAIL PROTECTED]:/usr/lib/postgresql/7.4/bin$ psql -U > airburst airburst -p 5433 > > I get: > psql: FATAL: IDENT authentication failed for user "airburst" > > REFERRING TO /etc/postgresql/7.4/main > My pg_hba.conf has: > # IPv4-style local connections: > hostall all 127.0.0.1 > 255.255.255.255 trust# RNS added > > My postgresql.conf has: > syslog = 2 # 0 # range 0-2; 0=stdout; 1=both; > 2=syslog > client_min_messages = debug1 > log_min_messages = debug1 > log_min_error_statement = error > > YET I'm getting NO logging in either /var/log/syslog nor in /var/log/ > postgresql/postgresql-7.4-main.log > > WHY? > Why no logging > and Why unable to connect? I don't know why you're not getting any logging, on my ubuntu 7.4 laptop, it just works. However, on the logging in, you want to edit your pg_hba.conf file. There's a page on it in the docs, but it's pretty much self-documented with lots of comments inside it. Then reload or restart pgsql to make the changes take effect. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question regarding Hibernate ORDER BY issue
Valerie Cole wrote:
> I have a problem and am pretty sure Hibernate is the culprit but was
> wondering if anybody knew of a fix. We are working on legacy code and
> converted a class from Hibernate 2 xml mappings to Hibernate 3 with
> annotations. On one of the One To Many attributes we have used the
> @OrderBy("displayPosition"). The SQL generated by Hibernate outputs the
> column name as DisplayPosition with no quoting, and Postgres kicks back
> an error saying the column does not exist.
Did you try adding extra double quotes? Something like
@OrderBy("\"displayPosition\"").
> Our tables/columns have all been created with quotes and must be
> accessed with quotes (I don't know if that is the norm, I am somewhat
> of a Postgres newb).
This is correct, unless your table/column names (a.k.a. "identifiers")
are lowercase in which case you can leave the quotes out.
--
Alvaro Herrera http://www.flickr.com/photos/alvherre/
A male gynecologist is like an auto mechanic who never owned a car.
(Carrie Snow)
---(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] Questions about LIMIT/OFFSET
On Fri, 19 Oct 2007 18:19:55 -0500 Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Oct 19, 2007, at 16:03 , Josh Trutwin wrote: > > > SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y; > > The server will have to generate at most OFFSET + LIMIT rows, > returning LIMIT rows or fewer. > > > SELECT * FROM table WHERE foo="bar" ORDER BY abc; > > This will return all of the rows available. > > Unless you're going to be returning all of the rows where > foo="bar" (e.g., executing multiple LIMIT OFFSET queries) in one > request, I should think the first query would be more performant: > fewer rows for the server to process (in the final step at least) > and less data transmitted between the server and your application. Thanks - server and application are on the same box so not as big a concern, but this is the way I decided to go for the time being. Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Connection & logging Problems
I have both 7.4 & 8.2 on this box. Everything below is WRT v7.4 User postgres connects fine. When I: [EMAIL PROTECTED]:/usr/lib/postgresql/7.4/bin$ psql -U airburst airburst -p 5433 I get: psql: FATAL: IDENT authentication failed for user "airburst" REFERRING TO /etc/postgresql/7.4/main My pg_hba.conf has: # IPv4-style local connections: hostall all 127.0.0.1 255.255.255.255 trust# RNS added My postgresql.conf has: syslog = 2 # 0 # range 0-2; 0=stdout; 1=both; 2=syslog client_min_messages = debug1 log_min_messages = debug1 log_min_error_statement = error YET I'm getting NO logging in either /var/log/syslog nor in /var/log/ postgresql/postgresql-7.4-main.log WHY? Why no logging and Why unable to connect? Ralph Smith [EMAIL PROTECTED] = ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Questions about LIMIT/OFFSET
On Oct 19, 2007, at 16:03 , Josh Trutwin wrote: SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y; The server will have to generate at most OFFSET + LIMIT rows, returning LIMIT rows or fewer. SELECT * FROM table WHERE foo="bar" ORDER BY abc; This will return all of the rows available. Unless you're going to be returning all of the rows where foo="bar" (e.g., executing multiple LIMIT OFFSET queries) in one request, I should think the first query would be more performant: fewer rows for the server to process (in the final step at least) and less data transmitted between the server and your application. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL Conference Fall 07 -- Registration Closed
Hello, Thanks to everyone who registered, the numbers are quite a bit above what we expected but alas, all good things come to an end (temporarily). Registration for the conference is now closed! See you all tomorrow morning! And of course, we can't but say at least one more final thank you to our sponsors: Command Prompt - http://www.commandprompt.com/ Continuent - http://www.continuent.com/ EnterpriseDB - http://www.enterprisedb.com/ GreenPlum - http://www.greenplum.com/ Hyperic - http://www.hyperic.com/ LinuxFund - http://www.linuxfund.org/ OmniTI - http://www.omniti.com/ OTG - http://www.otg-nc.com/ Sun - http://www.sun.com/ Truviso - http://www.truviso.com/ Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
[GENERAL] Question regarding Hibernate ORDER BY issue
Hello
I have a problem and am pretty sure Hibernate is the culprit but was
wondering if anybody knew of a fix. We are working on legacy code and
converted a class from Hibernate 2 xml mappings to Hibernate 3 with
annotations. On one of the One To Many attributes we have used the
@OrderBy("displayPosition"). The SQL generated by Hibernate outputs the
column name as DisplayPosition with no quoting, and Postgres kicks back
an error saying the column does not exist. Our tables/columns have all
been created with quotes and must be accessed with quotes (I don't know
if that is the norm, I am somewhat of a Postgres newb). I have been
Googling for about an hour and unable to come up with anything, so I
thought I would drop a line.
Thanks,
V. Cole
__
Valerie Cole
Technical Services
Wirestone, LLC
Re: [GENERAL] ERROR: Could not access status of transaction ####
On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: Thank you Scott! I'm away from my desk and will dive back into it. Fortunately I have two machines, each w/ 7.4 & 8.2 on them. New installs on Ubuntu 7.4. As to why I had 'no role or database' errors yesterday, am I right that it was either: A) I accidentally did a pg_dump when I thought I'd done a pg_dumpall, or B) Using the text file output of pg_dumpall behaves differently on import than the -Fc format? I'd imported it w/ psql, since it was a text file. == On Oct 19, 2007, at 2:06 PM, Scott Marlowe wrote: Most likely the first. Without a pg_dumpall you don't get the accounts. Note that you can do just a pg_dumpalll -g to get the "global" data, which includes the accounts. -- I'm making progess, but SLOWLY... I now have a new db postgres, and now my targed db airburst I have both 7.4 & 8.2 on this box. When I: [EMAIL PROTECTED]:/usr/lib/postgresql/7.4/bin$ psql -U airburst airburst -p 5433 I get: psql: FATAL: IDENT authentication failed for user "airburst" REFERRING TO /etc/postgresql/7.4/main My pg_hba.conf has: # IPv4-style local connections: hostall all 127.0.0.1 255.255.255.255 trust# RNS added My postgresql.conf has: syslog = 2 # 0 # range 0-2; 0=stdout; 1=both; 2=syslog client_min_messages = debug1 log_min_messages = debug1 log_min_error_statement = error YET I'm getting NO logging in either /var/log/syslog nor in /var/log/ postgresql/postgresql-7.4-main.log WHY? Why no logging and Why unable to connect? User postgres connects fine. One more thanks! Ralph Smith [EMAIL PROTECTED] = ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] uniquely indexing Celko's nested set model
Is it possible to constraint both the LEFT and RIGHT fields of a record to use the same index? I am looking for a way to ensure for all LEFTs and RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to same value. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] install failed with "not NTFS filesystem"
Hi, I'm trying to install a Postgresql 8.1.10 in one external drive (Lacie 150GB, USB connection) attached into a Windows XP SP2 system. The installation was interrupted with the message: "PostgreSQL data directory must be on NTFS formatted volume". The HD Lacie is new and formatted in NTFS. NTFS is a default on Format Window and I did not change this. Driver properties show that is an NTFS filesystem: Windows Explorer and Control Panel/Administrative Tools. What can be wrong? Thanks in advance, Josi Perez
Re: [GENERAL] Order-independent multi-field uniqueness constraint?
"Kynn Jones" <[EMAIL PROTECTED]> writes:
> CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement )
> RETURNS anyarray AS
> $$
> BEGIN
> IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ];
> ELSERETURN ARRAY[ $2, $1 ];
> END IF;
> END;
> $$ LANGUAGE plpgsql;
You need to add IMMUTABLE as well.
> and this function works as expected, but when I try to use it in a
> constraint I get the error:
>
> -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
> ERROR: 42601: syntax error at or near "("
> LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
What you need is:
CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y));
> LOCATION: base_yyerror, scan.l:795
>
> I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK
> syntax but not UNIQUE(my_function(x)).
Really? It doesn't work for me in the ADD CONSTRAINT syntax. I don't think you
can use the ADD CONSTRAINT syntax, you have to use the CREATE UNIQUE INDEX
syntax. It's effectively the same in Postgres anyways.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: Could not access status of transaction ####
On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > Thank you Scott! > > I'm away from my desk and will dive back into it. > Fortunately I have two machines, each w/ 7.4 & 8.2 on them. New > installs on Ubuntu 7.4. > > As to why I had 'no role or database' errors yesterday, am I right > that it was either: > A) I accidentally did a pg_dump when I thought I'd done a > pg_dumpall, or > B) Using the text file output of pg_dumpall behaves differently on > import than the -Fc format? > I'd imported it w/ psql, since it was a text file. Most likely the first. Without a pg_dumpall you don't get the accounts. Note that you can do just a pg_dumpalll -g to get the "global" data, which includes the accounts. ---(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] Questions about LIMIT/OFFSET
I'm going to be using a smarty plugin to paginate some result sets for display in smarty templates. I was reading that using LIMIT/OFFSET generates multiple query plans so I'm curious if it would be better to do a: SELECT * FROM table WHERE foo="bar" ORDER BY abc LIMIT x OFFSET y; or just: SELECT * FROM table WHERE foo="bar" ORDER BY abc; and create my result set array for my templates using application ode - increasing the likelihood of pulling the above query from the cache? I'm sure the answer is "it depends" but curious what others do with this? Thanks, Josh ---(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] Abbreviation list
Hi, Le Friday 19 October 2007 22:29:37 Steve Crawford, vous avez écrit : > > Do we use that term? Normally for us it's "large objects". > > Perhaps we should add "LO" but the documentation does refer to the term > BLOB though typically in the context of "The SQL standard defines a > different binary string type, called BLOB..." or in the list of reserved > words. This topic was discussed on IRC the other day, and we seemed to conclude that what standard SQL refers to as CLOB and BLOB can be compared to PostgreSQL text and bytea types with TOAST storage, which makes them out-of-line text or binary objects. Large Objects seems to be another beast when compared to blobs... As I'm still pretty ignorant on the matter, though, I'd appreciate it if some light was to be made ;) Regards, -- dim ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: Could not access status of transaction ####
Thank you Scott! I'm away from my desk and will dive back into it. Fortunately I have two machines, each w/ 7.4 & 8.2 on them. New installs on Ubuntu 7.4. As to why I had 'no role or database' errors yesterday, am I right that it was either: A) I accidentally did a pg_dump when I thought I'd done a pg_dumpall, or B) Using the text file output of pg_dumpall behaves differently on import than the -Fc format? I'd imported it w/ psql, since it was a text file. Thanks! Ralph Smith [EMAIL PROTECTED] = On Oct 19, 2007, at 1:35 PM, Scott Marlowe wrote: On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: I'm using 7.4 in preparation for an overdue upgrade. Yesterday I posted 2 questions that were ignored, so I'll try a third now. When doing a query I get same error as below, which are the results of vacuumdb. vacuumdb: vacuuming of database "airburst" failed: ERROR: could not access status of transaction 7564911 DETAIL: could not open file "/var/lib/postgresql/7.4/main/pg_clog/0007": No such file or directory I'm only querying from 1 table. I could really use some help. Regarding yesterday's Qs, how best to un-import from pg_dumpall's results? I'd dump-all'd into a text file and imported it via a psql -f filename. It had encoding errors AND did NOT make the database I needed. Everything went into db postgres. yeah, you should be able to just drop and recreate postgres. As the postgres superuser, something like: dropdb postgres createdb -T template1 postgres OR if it fails not finding the postgres db, then psql template1 -U postgres create database postgres with template template1 As for the encoding, make a new db with the same encoding as your last db, and try again. If that doesn't work, look up iconv in the archives / google. Lets you convert from encoding to another. The vacuumdb error is much more worrisome and makes me wonder about your hardware / OS reliability / possible driver error. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: Could not access status of transaction ####
On 10/19/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > I'm using 7.4 in preparation for an overdue upgrade. > > Yesterday I posted 2 questions that were ignored, so I'll try a third now. > > When doing a query I get same error as below, which are the results of > vacuumdb. > > vacuumdb: vacuuming of database "airburst" failed: ERROR: could not access > status of transaction 7564911 > DETAIL: could not open file > "/var/lib/postgresql/7.4/main/pg_clog/0007": No such file > or directory > > I'm only querying from 1 table. > I could really use some help. > > > > Regarding yesterday's Qs, how best to un-import from pg_dumpall's results? > I'd dump-all'd into a text file and imported it via a psql -f filename. > It had encoding errors AND did NOT make the database I needed. Everything > went into db postgres. yeah, you should be able to just drop and recreate postgres. As the postgres superuser, something like: dropdb postgres createdb -T template1 postgres OR if it fails not finding the postgres db, then psql template1 -U postgres create database postgres with template template1 As for the encoding, make a new db with the same encoding as your last db, and try again. If that doesn't work, look up iconv in the archives / google. Lets you convert from encoding to another. The vacuumdb error is much more worrisome and makes me wonder about your hardware / OS reliability / possible driver error. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Abbreviation list
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/19/07 14:36, Alvaro Herrera wrote: > Steve Crawford wrote: >> Bruce Momjian wrote: >>> Tom Lane wrote: Steve Crawford <[EMAIL PROTECTED]> writes: > My vote is to add "Appendix I. Abbreviations". It seems more like FAQ material than something for the manual. >>> I prefer the manual. I would think the list would be pretty long and >>> deal with lots of internals terms. >> My vote too. Just noticed I missed (probably one of many): >> BLOB - Binary Large Object > > Do we use that term? Normally for us it's "large objects". Good news everyone! "Large objects" is generic enough that if PG is ever ported to the IBM 1400 that you won't have to come up with a new acronym: DLOB (Decimal Large OBject). - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHGRT8S9HxQb37XmcRAvnnAJwO9RVzeNwFWh5hCdQNnUihDuy2QQCeJyUL SHMDqqSUmIPbTLU5d+/LmKI= =y/Vu -END PGP SIGNATURE- ---(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] Abbreviation list
Alvaro Herrera wrote: > Steve Crawford wrote: >> Bruce Momjian wrote: >>> Tom Lane wrote: Steve Crawford <[EMAIL PROTECTED]> writes: > My vote is to add "Appendix I. Abbreviations". It seems more like FAQ material than something for the manual. >>> I prefer the manual. I would think the list would be pretty long and >>> deal with lots of internals terms. >> My vote too. Just noticed I missed (probably one of many): >> BLOB - Binary Large Object > > Do we use that term? Normally for us it's "large objects". > Perhaps we should add "LO" but the documentation does refer to the term BLOB though typically in the context of "The SQL standard defines a different binary string type, called BLOB..." or in the list of reserved words. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] running postgres
On Thu, Oct 18, 2007 at 10:50:02AM -0500, Scott Marlowe <[EMAIL PROTECTED]> wrote a message of 18 lines which said: > Personally, I found it quite easy. > > apt-get install postgresqlp8.2 > sudo /etc/init.d/postgres-8.2 start > sudo su - postgres > psql > > 4 whole steps. Although I regard PostgreSQL as quite simple and easy to use (I find it easier than MySQL, for instance), your oversimplication won't help the potential users to evalute PostgreSQL. For instance, I typically spend a lot of time in step 5, editing pg_hba.conf and struggling to obtain the desired effect. Also, on non-Debian platforms, step 1 can be more complicated if I want other languages (I use Python a lot in my stored procedures). ---(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] ERROR: Could not access status of transaction ####
I'm using 7.4 in preparation for an overdue upgrade. Yesterday I posted 2 questions that were ignored, so I'll try a third now. When doing a query I get same error as below, which are the results of vacuumdb. vacuumdb: vacuuming of database "airburst" failed: ERROR: could not access status of transaction 7564911 DETAIL: could not open file "/var/lib/postgresql/7.4/main/pg_clog/ 0007": No such file or directory I'm only querying from 1 table. I could really use some help. Regarding yesterday's Qs, how best to un-import from pg_dumpall's results? I'd dump-all'd into a text file and imported it via a psql -f filename. It had encoding errors AND did NOT make the database I needed. Everything went into db postgres. Please help. Ralph Smith [EMAIL PROTECTED] =
Re: [GENERAL] Abbreviation list
On Oct 19, 2007, at 1:20 PM, Steve Crawford wrote: Bruce Momjian wrote: Tom Lane wrote: Steve Crawford <[EMAIL PROTECTED]> writes: My vote is to add "Appendix I. Abbreviations". It seems more like FAQ material than something for the manual. I prefer the manual. I would think the list would be pretty long and deal with lots of internals terms. My vote too. Just noticed I missed (probably one of many): BLOB - Binary Large Object As far as missed goes, I believe I saw OLAP but not OLTP. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On Wed, 17 Oct 2007 09:22:11 +0200 Magnus Hagander <[EMAIL PROTECTED]> wrote: > > Maybe we should put an #ifdef WIN32 into guc.c to limit > > max_connections to something we know the platform can stand? It'd > > be more comfortable if we understood exactly where the limit was, > > but I think I'd rather have an "I'm sorry Dave, I can't do that" > > than random-seeming crashes. > > Yeayh, that's probably a good idea - except we never managed to > figure out where the limit is. It appears to vary pretty wildly > between different machines, for reasons we don't really know why > (total RAM has some effect on it, but that's not the only one, for > example) How about we just emit a warning.. WARNING: Connections above 250 on Windows platforms may have unpredictable results. Joshua D. Drake > > //Magnus > > ---(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 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [GENERAL] Abbreviation list
Steve Crawford wrote: > Bruce Momjian wrote: > > Tom Lane wrote: > >> Steve Crawford <[EMAIL PROTECTED]> writes: > >>> My vote is to add "Appendix I. Abbreviations". > >> It seems more like FAQ material than something for the manual. > > > > I prefer the manual. I would think the list would be pretty long and > > deal with lots of internals terms. > > My vote too. Just noticed I missed (probably one of many): > BLOB - Binary Large Object Do we use that term? Normally for us it's "large objects". -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem of installation on Mac
Michael Glaesemann wrote: On Oct 19, 2007, at 13:50 , brian wrote: Michael Glaesemann wrote: On Oct 20, 2000, at 13:05 , Martin Gainty wrote: Martin, it continues to amaze me how you're able to predict and contribute to the discussion 7 years in advance! Michael Glaesemann grzm seespotcode net But temporal causality is preserved, as we only get to see his posts now (for forward-incrementing instances of "now"). (or something like that) Nah. Occam's razor: network lag :) "forward-incrementing" -- Ach, such redundancy! Oh, well, too late now. Or is it? ---(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 of installation on Mac
On 10/19/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote: > > On Oct 19, 2007, at 13:50 , brian wrote: > > > Michael Glaesemann wrote: > >> On Oct 20, 2000, at 13:05 , Martin Gainty wrote: > >> Martin, it continues to amaze me how you're able to predict and > >> contribute to the discussion 7 years in advance! > >> Michael Glaesemann > >> grzm seespotcode net > > > > But temporal causality is preserved, as we only get to see his > > posts now (for forward-incrementing instances of "now"). > > > > (or something like that) > > Nah. Occam's razor: network lag :) Still connecting with a half-duplex ARCnet card are we? ---(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 of installation on Mac
On Oct 19, 2007, at 13:50 , brian wrote: Michael Glaesemann wrote: On Oct 20, 2000, at 13:05 , Martin Gainty wrote: Martin, it continues to amaze me how you're able to predict and contribute to the discussion 7 years in advance! Michael Glaesemann grzm seespotcode net But temporal causality is preserved, as we only get to see his posts now (for forward-incrementing instances of "now"). (or something like that) Nah. Occam's razor: network lag :) Michael Glaesemann grzm seespotcode net ---(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 of installation on Mac
Michael Glaesemann wrote: On Oct 20, 2000, at 13:05 , Martin Gainty wrote: Martin, it continues to amaze me how you're able to predict and contribute to the discussion 7 years in advance! Michael Glaesemann grzm seespotcode net But temporal causality is preserved, as we only get to see his posts now (for forward-incrementing instances of "now"). (or something like that) brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Order-independent multi-field uniqueness constraint?
Kynn Jones wrote:
I have a table used to store information about pairs of items. This
information is independent of the order of the two items in the pair,
so having two records
X Y
Y X
in the table would be redundant. But as far as I can tell, this
situation would not violate a uniqueness constraint involving the two
fields.
I could add the original constraint that enforces some canonical
order, say X < Y (assuming that they are integer IDs), but I'm trying
to avoid this because it would lead to a significant complication of
many of my queries, which currently ascribe slightly different
semantics to the first and second members of the pair.
The only solution I could think of is to write a function that takes
the two elements as input and returns them in some canonical order:
CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement )
RETURNS anyarray AS
$$
BEGIN
IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ];
ELSERETURN ARRAY[ $2, $1 ];
END IF;
END;
$$ LANGUAGE plpgsql;
and this function works as expected, but when I try to use it in a
constraint I get the error:
-> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
ERROR: 42601: syntax error at or near "("
LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
^
LOCATION: base_yyerror, scan.l:795
I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK
syntax but not UNIQUE(my_function(x)).
But be that as it may, is there any way to enforce an
order-independent uniqueness constraint without forcing a canonical
ordering on the elements saved in the table.
I'm not sure that what you're doing is the best solution, but shouldn't
that be: "... foo_uniq_x_y UNIQUE(SELECT canonicalize(x,y))"?
brian
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Temp Table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/19/07 11:39, Bob Pawley wrote: > When I fetch a row, as in the following, how do I copy the row's data into a > temporary table so that I can process it?? > > begin work; > Declare loop_set Cursor >for Select one, two, three, four, five, six, seven from loop_import >order by loop_id ; > fetch next From loop_set; Wouldn't it be simpler to do: INSERT INTO some_temp SELECT field list FROM some_table; - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHGPp0S9HxQb37XmcRAlpUAKDTR/qG8VQxZJpt1IyqZp7l9Q+V0gCfSW54 ANpxcxz4AAOYddctMe4inJc= =1lk2 -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] Abbreviation list
Bruce Momjian wrote: > Tom Lane wrote: >> Steve Crawford <[EMAIL PROTECTED]> writes: >>> My vote is to add "Appendix I. Abbreviations". >> It seems more like FAQ material than something for the manual. > > I prefer the manual. I would think the list would be pretty long and > deal with lots of internals terms. > My vote too. Just noticed I missed (probably one of many): BLOB - Binary Large Object Cheers, Steve ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem of installation on Mac
On Oct 20, 2000, at 13:05 , Martin Gainty wrote: Martin, it continues to amaze me how you're able to predict and contribute to the discussion 7 years in advance! Michael Glaesemann grzm seespotcode net ---(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] Abbreviation list
On Fri, 19 Oct 2007 20:04:27 +0200 Michael Meskes <[EMAIL PROTECTED]> wrote: > On Thu, Oct 18, 2007 at 04:46:00PM -0700, Steve Crawford wrote: > > ECPG - Embedded SQL in C ?? > > Correct. And the PG part should be obvious. :-) Pretty Good? > > Michael -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [GENERAL] Abbreviation list
On Thu, Oct 18, 2007 at 04:46:00PM -0700, Steve Crawford wrote: > ECPG - Embedded SQL in C ?? Correct. And the PG part should be obvious. :-) Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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 of installation on Mac
assume the tablename you want to determine privs and access is called 'TABLENAME' SELECT priv, access_lvl FROM TABLENAME WHERE user = 'pgsql' M-- - Original Message - From: [EMAIL PROTECTED] To: Shane Ambler Cc: pgsql-general@postgresql.org Sent: Friday, October 19, 2007 12:19 PM Subject: Re: [GENERAL] Problem of installation on Mac If I understand correctly, the user "pgsql", besides having its own file system not related to others, is the administrator of the postgresql db administrator. Only "pgsql" can modify the db, but the other users can query the db but not modify it. Am I rigth? What I should do, if other users need to update the db. Thank you very much. Shane Ambler <[EMAIL PROTECTED]> 10/19/2007 12:08 PM To [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Problem of installation on Mac [EMAIL PROTECTED] wrote: > Thank you very much Shane. It fixed my problem, at least now :-). > > I installed the pgsql 8.2.5 from source. The user "postgres" has a > shortname of "pgsql". > > Another question, what's the purpose/advantage to create a user of > postgres? May I just create the database under my user account? > Technically you can run postgresql (and most other services) as almost any user - the reason you don't is security - being run as pgsql it can only read and write to files that that user account has access to. You would normally only allow it to write in the data directory setup for it. If it is hacked into it can only overwrite files in that one directory not your entire system. If it only needs limited access to the filesystem to run then don't give it access to everything. This also works the other way - if the pgsql user is the only one with read access to the data files then other users have trouble getting a copy of them. If you run ps aux (or activity monitor) you will see a few different user names listed for different services. > > Shane Ambler <[EMAIL PROTECTED]> > 10/18/2007 01:27 PM > > To > [EMAIL PROTECTED] > cc > pgsql-general@postgresql.org > Subject > Re: [GENERAL] Problem of installation on Mac > > > > > > > [EMAIL PROTECTED] wrote: >> Hi, I am a newbie for pgsql and tried to install it on my MacBookPro >> Intel. I got everything correct until creating the user: postgres and >> initdb, then the error message showed up for running the db. How to fix >> the problem? >> >> LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 & >> [1] 17837 >> LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied >> >> [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 >> LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu >> Password: >> LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 & >> [1] 17842 >> LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test >> createdb: could not connect to database postgres: could not connect to >> server: No such file or directory >> Is the server running locally and accepting >> connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >> [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 >> >> >> Thank you very much. >> >> >> L >> >> > > I would say the problem is when you are logged in as pgsql the >logfile > part tries to put the log file in the current working directory > (/Users/lizhexu in your example). Your pgsql user doesn't have > permission to write the log file there. When you are logged in as > lizhexu you don't get permission to read the data files. Also it should > be >>logfile changing >logfile to >>/usr/local/pgsql/data/logfile will > most likely fix the problem. > > I would suggest looking into contrib/start-scripts (from the source > distro) - look at PostgreSQL.darwin - the comments at the top should get > you started. These steps will have postgres running when you startup > your Mac. > > You will need to change PGUSER="postgres" to PGUSER="pgsql", leave the > rest and try running it. > > hint - with startup scripts if you don't want to restart you can use - > sudo /Library/StartupItems/PostgreSQL/PostgreSQL start > > > > Also which version are you installing and did you get a binary package > somewhere or are you building from source? > > > -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz
[GENERAL] Order-independent multi-field uniqueness constraint?
I have a table used to store information about pairs of items. This
information is independent of the order of the two items in the pair,
so having two records
X Y
Y X
in the table would be redundant. But as far as I can tell, this
situation would not violate a uniqueness constraint involving the two
fields.
I could add the original constraint that enforces some canonical
order, say X < Y (assuming that they are integer IDs), but I'm trying
to avoid this because it would lead to a significant complication of
many of my queries, which currently ascribe slightly different
semantics to the first and second members of the pair.
The only solution I could think of is to write a function that takes
the two elements as input and returns them in some canonical order:
CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement )
RETURNS anyarray AS
$$
BEGIN
IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ];
ELSERETURN ARRAY[ $2, $1 ];
END IF;
END;
$$ LANGUAGE plpgsql;
and this function works as expected, but when I try to use it in a
constraint I get the error:
-> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
ERROR: 42601: syntax error at or near "("
LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
^
LOCATION: base_yyerror, scan.l:795
I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK
syntax but not UNIQUE(my_function(x)).
But be that as it may, is there any way to enforce an
order-independent uniqueness constraint without forcing a canonical
ordering on the elements saved in the table.
TIA!
kj
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Generating sql to capture fully qualified table names??? - THANKS
THANK YOU BOTH ON YOUR REPLIES...THIS IS WHAT I WAS LOOKING FOR... -- View this message in context: http://www.nabble.com/Generating-sql-to-capture-fully-qualified-table-namestf4654460.html#a13299646 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Abbreviation list
On Fri, 19 Oct 2007 14:28:30 -0300 Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Joshua D. Drake wrote: > > On Fri, 19 Oct 2007 10:59:20 -0300 > > Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > > Thomas Pundt wrote: > > > > Alban Hertroys schrieb: > > > > > > >> Isn't this just what the ABBR tag in html is for? > > > > > > > > Well, yes; according w3.org its purpose is to describe the > > > > abbreviation/acronym inline in a document using a title > > > > attribute: > > > > > > Can you do that from a SGML DocBook source? > > > > > http://www.oasis-open.org/docbook/documentation/reference/html/acronym.html > > That seems pretty useless. > *shrug* Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [GENERAL] Abbreviation list
Joshua D. Drake wrote: > On Fri, 19 Oct 2007 10:59:20 -0300 > Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > Thomas Pundt wrote: > > > Alban Hertroys schrieb: > > > > >> Isn't this just what the ABBR tag in html is for? > > > > > > Well, yes; according w3.org its purpose is to describe the > > > abbreviation/acronym inline in a document using a title attribute: > > > > Can you do that from a SGML DocBook source? > > > http://www.oasis-open.org/docbook/documentation/reference/html/acronym.html That seems pretty useless. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Generating sql to capture fully qualified table names???
smiley2211 escreveu:
Hello all,
I am using the query below to generate SQL code to grant access to objects -
how do I get this statement to PULL the fully qualified name
(schema.tablename)???
*
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || relname ||
' TO newuser;'
FROM pg_class
where relname !~ 'pg_*' AND relkind in ('r','v','S') ORDER BY relname;
*
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||
pn.nspname || '.' || pc.relname ||' TO newuser;'
FROM pg_class pc JOIN pg_namespace pn
ON (pc.relnamespace = pn.oid)
WHERE pc.relname !~ 'pg_*' AND relkind in ('r','v','S')
AND pn.nspname != 'information_schema'
ORDER BY relname;
Osvaldo
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Abbreviation list
On Fri, 19 Oct 2007 10:59:20 -0300 Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Thomas Pundt wrote: > > Alban Hertroys schrieb: > > >> Isn't this just what the ABBR tag in html is for? > > > > Well, yes; according w3.org its purpose is to describe the > > abbreviation/acronym inline in a document using a title attribute: > > Can you do that from a SGML DocBook source? > http://www.oasis-open.org/docbook/documentation/reference/html/acronym.html -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [GENERAL] Generating sql to capture fully qualified table names???
On Oct 19, 2007, at 11:44 AM, smiley2211 wrote:
Hello all,
I am using the query below to generate SQL code to grant access to
objects -
how do I get this statement to PULL the fully qualified name
(schema.tablename)???
*
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || relname ||
' TO newuser;'
FROM pg_class
where relname !~ 'pg_*' AND relkind in ('r','v','S') ORDER BY
relname;
*
SELECT 'GRANT ALL ON ' || n.nspname || '.' || c.relname ' TO newuser;'
FROM pg_class c, pg_namespace c
WHERE c.relnamespace=n.oid
and n.nspname = 'your_schema_name';
Erik Jones
Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Generating sql to capture fully qualified table names???
Hello all,
I am using the query below to generate SQL code to grant access to objects -
how do I get this statement to PULL the fully qualified name
(schema.tablename)???
*
SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || relname ||
' TO newuser;'
FROM pg_class
where relname !~ 'pg_*' AND relkind in ('r','v','S') ORDER BY relname;
*
Thanks...Michelle :confused:
--
View this message in context:
http://www.nabble.com/Generating-sql-to-capture-fully-qualified-table-namestf4654460.html#a13298439
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Temp Table
When I fetch a row, as in the following, how do I copy the row's data into a temporary table so that I can process it?? begin work; Declare loop_set Cursor for Select one, two, three, four, five, six, seven from loop_import order by loop_id ; fetch next From loop_set; Bob Pawley
Re: [GENERAL] Problem of installation on Mac
If I understand correctly, the user "pgsql", besides having its own file system not related to others, is the administrator of the postgresql db administrator. Only "pgsql" can modify the db, but the other users can query the db but not modify it. Am I rigth? What I should do, if other users need to update the db. Thank you very much. Shane Ambler <[EMAIL PROTECTED]> 10/19/2007 12:08 PM To [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Problem of installation on Mac [EMAIL PROTECTED] wrote: > Thank you very much Shane. It fixed my problem, at least now :-). > > I installed the pgsql 8.2.5 from source. The user "postgres" has a > shortname of "pgsql". > > Another question, what's the purpose/advantage to create a user of > postgres? May I just create the database under my user account? > Technically you can run postgresql (and most other services) as almost any user - the reason you don't is security - being run as pgsql it can only read and write to files that that user account has access to. You would normally only allow it to write in the data directory setup for it. If it is hacked into it can only overwrite files in that one directory not your entire system. If it only needs limited access to the filesystem to run then don't give it access to everything. This also works the other way - if the pgsql user is the only one with read access to the data files then other users have trouble getting a copy of them. If you run ps aux (or activity monitor) you will see a few different user names listed for different services. > > Shane Ambler <[EMAIL PROTECTED]> > 10/18/2007 01:27 PM > > To > [EMAIL PROTECTED] > cc > pgsql-general@postgresql.org > Subject > Re: [GENERAL] Problem of installation on Mac > > > > > > > [EMAIL PROTECTED] wrote: >> Hi, I am a newbie for pgsql and tried to install it on my MacBookPro >> Intel. I got everything correct until creating the user: postgres and >> initdb, then the error message showed up for running the db. How to fix >> the problem? >> >> LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 & >> [1] 17837 >> LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied >> >> [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 >> LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu >> Password: >> LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 & >> [1] 17842 >> LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test >> createdb: could not connect to database postgres: could not connect to >> server: No such file or directory >> Is the server running locally and accepting >> connections on Unix domain socket "/tmp/.s.PGSQL.5432"? >> [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D >> /usr/local/pgsql/data >logfile 2>&1 >> >> >> Thank you very much. >> >> >> L >> >> > > I would say the problem is when you are logged in as pgsql the >logfile > part tries to put the log file in the current working directory > (/Users/lizhexu in your example). Your pgsql user doesn't have > permission to write the log file there. When you are logged in as > lizhexu you don't get permission to read the data files. Also it should > be >>logfile changing >logfile to >>/usr/local/pgsql/data/logfile will > most likely fix the problem. > > I would suggest looking into contrib/start-scripts (from the source > distro) - look at PostgreSQL.darwin - the comments at the top should get > you started. These steps will have postgres running when you startup > your Mac. > > You will need to change PGUSER="postgres" to PGUSER="pgsql", leave the > rest and try running it. > > hint - with startup scripts if you don't want to restart you can use - > sudo /Library/StartupItems/PostgreSQL/PostgreSQL start > > > > Also which version are you installing and did you get a binary package > somewhere or are you building from source? > > > -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz
Re: [GENERAL] Problem of installation on Mac
[EMAIL PROTECTED] wrote: Thank you very much Shane. It fixed my problem, at least now :-). I installed the pgsql 8.2.5 from source. The user "postgres" has a shortname of "pgsql". Another question, what's the purpose/advantage to create a user of postgres? May I just create the database under my user account? Technically you can run postgresql (and most other services) as almost any user - the reason you don't is security - being run as pgsql it can only read and write to files that that user account has access to. You would normally only allow it to write in the data directory setup for it. If it is hacked into it can only overwrite files in that one directory not your entire system. If it only needs limited access to the filesystem to run then don't give it access to everything. This also works the other way - if the pgsql user is the only one with read access to the data files then other users have trouble getting a copy of them. If you run ps aux (or activity monitor) you will see a few different user names listed for different services. Shane Ambler <[EMAIL PROTECTED]> 10/18/2007 01:27 PM To [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Problem of installation on Mac [EMAIL PROTECTED] wrote: Hi, I am a newbie for pgsql and tried to install it on my MacBookPro Intel. I got everything correct until creating the user: postgres and initdb, then the error message showed up for running the db. How to fix the problem? LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & [1] 17837 LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu Password: LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & [1] 17842 LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 Thank you very much. L I would say the problem is when you are logged in as pgsql the >logfile part tries to put the log file in the current working directory (/Users/lizhexu in your example). Your pgsql user doesn't have permission to write the log file there. When you are logged in as lizhexu you don't get permission to read the data files. Also it should be >>logfile changing >logfile to >>/usr/local/pgsql/data/logfile will most likely fix the problem. I would suggest looking into contrib/start-scripts (from the source distro) - look at PostgreSQL.darwin - the comments at the top should get you started. These steps will have postgres running when you startup your Mac. You will need to change PGUSER="postgres" to PGUSER="pgsql", leave the rest and try running it. hint - with startup scripts if you don't want to restart you can use - sudo /Library/StartupItems/PostgreSQL/PostgreSQL start Also which version are you installing and did you get a binary package somewhere or are you building from source? -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Re : [GENERAL] Abbreviation list
Alvaro Herrera wrote: > Steve Crawford wrote: >> Raymond O'Donnell wrote: >>> On 18/10/2007 22:26, Laurent ROCHE wrote: >>> No idea what GUC is, though ! >>> Grand Unified Contraption? ;-) >>> >>> Ray (who's just been reading Jules Verne). >> It's Global User Configuration. But the confusion does point out the >> need for a reference. > > I think it is Grand Unified Configuration actually ... what does Verne > use? I don't know the reference. > I knew someone would correct any errors. I searched high and low and stopped when I saw the first reasonable sounding explanation: http://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html Searching Google'"Global User Configuration" postgresql guc' yields 700 results. Altering that to "Grand Unified Configuration" yields 1,570. Confusion apparently abounds. Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Out of memory with ODBC
> From: Richard Huxton [mailto:[EMAIL PROTECTED] > Relyea, Mike wrote: > > This seems to be a problem with the ODBC driver? How can I narrow > > that down further? > > Well, first make 100% certain the query being executed is the > one you see being sent from Access. I did by setting log_statement = 'all' and they were. > It will also let you turn statement logging on in that > database (ALTER DATABASE SET ...) and capture everything the > ODBC driver sends. There might be something that leaps out at > you. Take all of those statements and put them into a > text-file and run them using psql -f . That should > cause the same crash. After setting log_statement = 'all', I ran my query using pgAdmin, and then ran the query using Access. I now had all of the commands sent to the DB by each application. > Remember, *something* in the sequence of commands that get > executed from Access must be different than when you execute > them through pgAdmin. That's exactly what it turned out to be. I looked at only the SET statements issued by each application and the differences popped right out at me. Turns out that Access was sending "set geqo to 'OFF'" and pgAdmin wasn't. As soon as I adjusted that setting in my connection string the problem went away. I'm not sure why it was off to begin with anyway. Thanks for your help! Mike ---(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] Abbreviation list
Thomas Pundt wrote: > Alban Hertroys schrieb: >> Isn't this just what the ABBR tag in html is for? > > Well, yes; according w3.org its purpose is to describe the > abbreviation/acronym inline in a document using a title attribute: Can you do that from a SGML DocBook source? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Abbreviation list
Alban Hertroys schrieb: Steve Crawford wrote: My vote is to add "Appendix I. Abbreviations". Don't know if it's practical for 8.3 documentation but it would be nice to add even if it only has a few entries as additional ones could be collected via the user notes. I suggest as a discussion starting-point the following inclusion criteria: 1. Any abbreviation/acronym that appears in the PostgreSQL documentation (even if those terms may not be PG specific - we shouldn't assume that everyone knows them). Good documentation practice recommends defining abbreviations the first time they are used. Better still, ensure that they are in the abbreviation list. Isn't this just what the ABBR tag in html is for? Well, yes; according w3.org its purpose is to describe the abbreviation/acronym inline in a document using a title attribute: GUC Firefox by default underlines the abbreviation with a dotted line and displays the title when leaving the cursor on it. In a way this means probably defining the abbreviation. Reference: http://www.w3.org/TR/html401/struct/text.html#edef-ABBR -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(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 : Re : Re : [GENERAL] Resetting SEQUENCEs
Hi,
if anybody needs something like this here is the request to produce the code to
resynch SEQUENCEs with the data in tha tables:
SELECT 'SELECT SETVAL(\'' ||S.relname|| '\', MAX(' ||C.attname|| ') ) FROM '
||T.relname|| ' ;'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
ORDER BY S.relname
This goes through all the sequences in current shema and generates SELECT
SETVAL(... orders to set the SEQUENCE to the last value in the table.
This only works it the SEQUENCE is OWNED (ALTER SEQUENCE ... OWNED BY ... since
8.2 or SERIAL) by a column: this way, the above SELECT will "find" the matching
column and table for a SEQUENCE.
Note : this will not work for SEQUENCES not linked to tables, you could find a
way by working out the table name and column name from the sequence name (for
instance by using a similar naming convention to the SERIAL sequences).
Thanks for all the help I got.
Cheers,
[EMAIL PROTECTED]
The Computing Froggy
- Message d'origine
De : Alvaro Herrera <[EMAIL PROTECTED]>
À : Laurent ROCHE <[EMAIL PROTECTED]>
Cc : Martijn van Oosterhout <[EMAIL PROTECTED]>; pgsql-general@postgresql.org
Envoyé le : Jeudi, 18 Octobre 2007, 23h54mn 16s
Objet : Re: Re : Re : [GENERAL] Resetting SEQUENCEs
Laurent ROCHE wrote:
> Hi,
>
> So nobody can help me to write the SELECT that will return the
SEQUENCE names, and their linked columns and their linked tables ?
> Are the system tables documented somewhere ?
Of course -- in the "internals" section. You need pg_class where
relkind = 's', pg_depend, and possibly pg_attribute.
--
Alvaro Herrera
http://www.PlanetPostgreSQL.org/
"La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva"
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Stalled post to pgsql-bugs
Thanks! I thought that my bug was ignored...
It would be nice to have RETURNING used in PL/SQL functions...
Without named parameters I can live -- they are just syntactic sugar
-- but RETURNING can not be worked around...
Ciprian.
On 10/19/07, Pavel Stehule <[EMAIL PROTECTED]> wrote:
> Hello
>
> it's feature :(. And it isn't supported in 8.3 too
>
> all info about unsupported features you can find in ToDo list
> http://www.postgresql.org/docs/faqs.TODO.html
>
> # Allow SQL-language functions to return results from RETURNING queries
>
> http://archives.postgresql.org/pgsql-hackers/2006-10/msg00665.php
> # Allow SQL-language functions to reference parameters by parameter name
>
> Currently SQL-language functions can only refer to dollar parameters, e.g. $1
>
> Regards
> Pavel Stehule
>
> 2007/10/19, Ciprian Dorin Craciun <[EMAIL PROTECTED]>:
> > Hello all!
> >
> > Almost two months ago I have posted a bug on the bug reporting
> > form that is available on the web page... (I forward here the email I
> > got in response.) It was related about the RETURNING feature available
> > for INSERT, UPDATE and DELETE, and the fact that I can not use it as
> > last statement is PL/SQL functions.
> >
> > But until now I saw no activity on this bug... How can I track it?
> > Has some one looked at it? Is it already fixed?
> >
> > I've monitored the release notes of every release from 8.2.5 and
> > saw nothing related to the issue.
> >
> > Could some one point me into the right direction? Or I could try
> > to fix it my self if someone could tell me where to start...
> >
> > Thank you all,
> > Ciprian.
> >
> > P.S.: I have also tried to send this same message directly to
> > psql-bugs mailing list but obtained the same message that the report
> > is stalled.
> >
> >
> > -- Forwarded message --
> > From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> > Date: Sep 3, 2007 8:13 PM
> > Subject: Stalled post to pgsql-bugs
> > To: Ciprian Dorin Craciun <[EMAIL PROTECTED]>
> >
> >
> > Your message to pgsql-bugs has been delayed, and requires the approval
> > of the moderators, for the following reason(s):
> >
> > The author ("Ciprian Dorin Craciun" <[EMAIL PROTECTED]>)
> > is not a member of any of the restrict_post groups.
> >
> > If you do not wish the message to be posted, or have other concerns,
> > please send a message to the list owners at the following address:
> > [EMAIL PROTECTED]
> >
> >
> > -- Forwarded message --
> > From: "Ciprian Dorin Craciun" <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Date: Mon, 3 Sep 2007 17:13:40 GMT
> > Subject: BUG #3596: "insert ... returning *" not usable as last
> > statement in a function...
> >
> > The following bug has been logged online:
> >
> > Bug reference: 3596
> > Logged by: Ciprian Dorin Craciun
> > Email address: [EMAIL PROTECTED]
> > PostgreSQL version: 8.2.4
> > Operating system: Debian Etch (4.0)
> > Description:"insert ... returning *" not usable as last statement in
> > a function...
> > Details:
> >
> > I think the best explanation is the example just above...
> >
> > The idea is that in 8.2 insert statement was modified by adding an returning
> > option, that modifies insert to behave also as a select statement by
> > returning all the rows inserted. But unfortunately this option can not be
> > used inside functions that return something as a last statement...
> >
> > create table table1 ( field int );
> >
> > create function function1 ( int ) returns table1 as $$
> > insert into table1 values ($1) returning *;
> > $$ language sql;
> >
> > ---(end of broadcast)---
> > TIP 4: Have you searched our list archives?
> >
> >http://archives.postgresql.org/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Stalled post to pgsql-bugs
Hello
it's feature :(. And it isn't supported in 8.3 too
all info about unsupported features you can find in ToDo list
http://www.postgresql.org/docs/faqs.TODO.html
# Allow SQL-language functions to return results from RETURNING queries
http://archives.postgresql.org/pgsql-hackers/2006-10/msg00665.php
# Allow SQL-language functions to reference parameters by parameter name
Currently SQL-language functions can only refer to dollar parameters, e.g. $1
Regards
Pavel Stehule
2007/10/19, Ciprian Dorin Craciun <[EMAIL PROTECTED]>:
> Hello all!
>
> Almost two months ago I have posted a bug on the bug reporting
> form that is available on the web page... (I forward here the email I
> got in response.) It was related about the RETURNING feature available
> for INSERT, UPDATE and DELETE, and the fact that I can not use it as
> last statement is PL/SQL functions.
>
> But until now I saw no activity on this bug... How can I track it?
> Has some one looked at it? Is it already fixed?
>
> I've monitored the release notes of every release from 8.2.5 and
> saw nothing related to the issue.
>
> Could some one point me into the right direction? Or I could try
> to fix it my self if someone could tell me where to start...
>
> Thank you all,
> Ciprian.
>
> P.S.: I have also tried to send this same message directly to
> psql-bugs mailing list but obtained the same message that the report
> is stalled.
>
>
> -- Forwarded message --
> From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> Date: Sep 3, 2007 8:13 PM
> Subject: Stalled post to pgsql-bugs
> To: Ciprian Dorin Craciun <[EMAIL PROTECTED]>
>
>
> Your message to pgsql-bugs has been delayed, and requires the approval
> of the moderators, for the following reason(s):
>
> The author ("Ciprian Dorin Craciun" <[EMAIL PROTECTED]>)
> is not a member of any of the restrict_post groups.
>
> If you do not wish the message to be posted, or have other concerns,
> please send a message to the list owners at the following address:
> [EMAIL PROTECTED]
>
>
> -- Forwarded message --
> From: "Ciprian Dorin Craciun" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Date: Mon, 3 Sep 2007 17:13:40 GMT
> Subject: BUG #3596: "insert ... returning *" not usable as last
> statement in a function...
>
> The following bug has been logged online:
>
> Bug reference: 3596
> Logged by: Ciprian Dorin Craciun
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.2.4
> Operating system: Debian Etch (4.0)
> Description:"insert ... returning *" not usable as last statement in
> a function...
> Details:
>
> I think the best explanation is the example just above...
>
> The idea is that in 8.2 insert statement was modified by adding an returning
> option, that modifies insert to behave also as a select statement by
> returning all the rows inserted. But unfortunately this option can not be
> used inside functions that return something as a last statement...
>
> create table table1 ( field int );
>
> create function function1 ( int ) returns table1 as $$
> insert into table1 values ($1) returning *;
> $$ language sql;
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>
---(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] Crosstab Problems
"Tom Lane" <[EMAIL PROTECTED]> writes: > 3. Throw a NOTICE or WARNING (hopefully only one message not repeated > ones) if NULL rowid is seen, then ignore the row. >From my experience with OLTP I don't like this one. A warning for DML is effectively the same as an error if you're running thousands of queries per minute. The logs fill up and even if you filter the logs it imposes extra run-time overhead. You end up having to avoid the warning just as if it had been an error. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Abbreviation list
Steve Crawford wrote: > My vote is to add "Appendix I. Abbreviations". Don't know if it's > practical for 8.3 documentation but it would be nice to add even if it > only has a few entries as additional ones could be collected via the > user notes. > > I suggest as a discussion starting-point the following inclusion criteria: > > 1. Any abbreviation/acronym that appears in the PostgreSQL documentation > (even if those terms may not be PG specific - we shouldn't assume that > everyone knows them). Good documentation practice recommends defining > abbreviations the first time they are used. Better still, ensure that > they are in the abbreviation list. Isn't this just what the ABBR tag in html is for? -- 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 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] Stalled post to pgsql-bugs
Hello all!
Almost two months ago I have posted a bug on the bug reporting
form that is available on the web page... (I forward here the email I
got in response.) It was related about the RETURNING feature available
for INSERT, UPDATE and DELETE, and the fact that I can not use it as
last statement is PL/SQL functions.
But until now I saw no activity on this bug... How can I track it?
Has some one looked at it? Is it already fixed?
I've monitored the release notes of every release from 8.2.5 and
saw nothing related to the issue.
Could some one point me into the right direction? Or I could try
to fix it my self if someone could tell me where to start...
Thank you all,
Ciprian.
P.S.: I have also tried to send this same message directly to
psql-bugs mailing list but obtained the same message that the report
is stalled.
-- Forwarded message --
From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Sep 3, 2007 8:13 PM
Subject: Stalled post to pgsql-bugs
To: Ciprian Dorin Craciun <[EMAIL PROTECTED]>
Your message to pgsql-bugs has been delayed, and requires the approval
of the moderators, for the following reason(s):
The author ("Ciprian Dorin Craciun" <[EMAIL PROTECTED]>)
is not a member of any of the restrict_post groups.
If you do not wish the message to be posted, or have other concerns,
please send a message to the list owners at the following address:
[EMAIL PROTECTED]
-- Forwarded message --
From: "Ciprian Dorin Craciun" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Date: Mon, 3 Sep 2007 17:13:40 GMT
Subject: BUG #3596: "insert ... returning *" not usable as last
statement in a function...
The following bug has been logged online:
Bug reference: 3596
Logged by: Ciprian Dorin Craciun
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system: Debian Etch (4.0)
Description:"insert ... returning *" not usable as last statement in
a function...
Details:
I think the best explanation is the example just above...
The idea is that in 8.2 insert statement was modified by adding an returning
option, that modifies insert to behave also as a select statement by
returning all the rows inserted. But unfortunately this option can not be
used inside functions that return something as a last statement...
create table table1 ( field int );
create function function1 ( int ) returns table1 as $$
insert into table1 values ($1) returning *;
$$ language sql;
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Crosstab Problems
But when re-doing the query now without the JOIN, it works (almost): SELECT * FROM crosstab( 'SELECT id_country AS id, year_start AS year, value FROM agri_area AS d WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 ORDER BY year_start ASC, id_country ASC;' , 3) AS ct(id int2, y_2003 numeric, y_2002 numeric, y_2001 numeric) Now, the problem is that it lists three times the IDs, and only the first year column is filled with values. The other two year columns stay empty. You missed this point in the docs: Notes 1. The sql result must be ordered by 1,2. Change your order by to that and it works fine. Oh, great. No, haven't seen it. Now it works. Thanks a lot! Just for the completeness, I attach the SQL. SELECT * FROM crosstab( 'SELECT COALESCE(c.name, ), year_start AS year, value FROM agri_area AS d LEFT JOIN countries AS c ON c.id = id_country WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 GROUP BY name, id_country, year_start, value ORDER BY 1,2;' , 3) AS ct(name varchar, y_2003 numeric, y_2002 numeric, y_2001 numeric) ---(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: re[GENERAL] lations does not exist
Adrian Klaver wrote: >> I doing a simple insert into a table re Perl/DBI >> "INSERT INTO party (party_id, party_type_id, description, status_id) >> VALUES ($partyId, 'PERSON', 'Initial Import','PARTY_ENABLED') >> >> and I'm getting a >> "ERROR: relations "party" does not exist" >> I get the same error message in pgadmin. >> >> The table "party" certainly exists. > > Two things come to mind. > 1) Do you have the necessary permissions to access the > schema table 'party' is in? > 2) How was the name for 'party' originally entered? It could > be a case sensitive problem. See > http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html > Section 4.1.1 for a complete explanation. Basically if the > table name was entered with quotes in a form other than 'party' then > selecting for 'party' will result in the error above. Other possibilities: - Your schema search_path is set to not include the schema that contains the table. - You are in the wrong database. What is the exact command with which you prove your claim that 'The table "party" certainly exists'? Is this command issued as the same user that runs the insert? Yours, Laurenz Albe ---(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
