Re: [GENERAL] Firebird and PostgreSQL at the DB Corral.
Paul Ganainm wrote: Hi all, Following up on another thread, here is a comparison between FB and PG from an FB'ers POV. BTW, FB is the love-child of Open-Source-Interbase. I would love to have this comparison in a table form and posted on web. Let FB guys chip in and make it more correct.. The architectures of the databases are fundamentally the same MVCC for you, MGA for us Firebirders (Multi Generational Architecture). And postgresql has vacuum and FB has automatic sweeps, correct? From an Open-Source-Interbase/Firebird point of view there are several issues where PostgreSQL falls down. Ease of use (particularly on Windows). FB is about as easy as installing Minesweeper. Native Postgresql on windows is practially non-exsitent so if you want to compare ease of use, let's talk unix where postgresql is fairly easy to use.. I'm not sure exactly where I stand here, but FB has been moved to C++, whereas AFAIK, PostgreSQL is still in C - this is probably a religious war (and beyond me), but I just thought that I'd mention it. Let's skip it. It is legacy.. Legendary reliability and stability Ditto for FB. Although there are reports of corruption from time to time, it is generally because the "server" was actually some end-user's PC. Can that be termed as data corruption due to fault in database server is rare. we should not attribute data corruption due to hardware failure to database software. Postgresql ranks very high on that regard. Except for WAL corruption bug fixed in 7.3.3, there has been no such bug till date(and even for long time) IIRC.. Extensible Ditto for FB. If you want to extend the code, though, you have to give those changes back to the community - the licence is more GPL than BSD. Postgresql is extensible from an applications POV. Data types, operators, functions, languages, rules, domains and checks etc. How much of it FB supports? >>Cross platform FB supports approx. 10 major platforms - not quite as many as PG, but still enough to be getting on with. Practiaclly postgresql supports one platform. Unix..:-) Rest is marketing speak. I am not discounting support matrix but I think this is fair to start a database comparison. Windows port in works. That will truely be another platform.. ANSI SQL compliant X Be careful.. You need to list SQL version as well.. Native interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, Python, and Ruby X caveat: I think that FB works natively with the vast majority of these and has its own interfaces as well, but not ECPG obviously. Does it support writing stored procedures in any of these?.. Rules ? I don't understand these - can somebody explain exactly what they are? This is answered already but I will take another shot. Rules allow you to redirect/add to action of an SQL statement. Check http://developer.postgresql.org/docs/postgres/rules.html Views X (FB's are updateable to boot!) You can do that in postgresql bu postgresql won't do it for you. You have to due the legwork.. Hot stand-by (commercial solutions) ? (not sure what is meant by this) Your database machine/service fails and it is automatically switched over to another database machine/servie without interruption in application availability.. You didn't cover one thing. The on disk layout. AFAIK, FB uses one database per file which makes it hard to support division of data physically. Postgresql has much better disk organisation IMHO.. Good to have such comparison. Let's hope to get a compiled version on web for masses to see.. Shridhar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Sequence name with SERIAL type
I'm curious if the default scheme for sequence name (which is created with SERIAL data type) can be changed -- currently all sequences are named like this: __seq -- can it be changed for e.g. seq ??? Thanks. ML ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Sequence question.
How about using two tables; one to hold the keyword and its (last allocated) sequence value, and the second to store your data as below. create table Keyword ( keyword varchar(32), sequence integer, primary key(keyword) ) create table Data ( id serial, sequence int, keyword varchar(32), text text ) Add a trigger to the Data table for Insert so that it joins to the (parent) keyword table and increments the keyword.sequence value, and places that into the Data.sequence value. You will get 'holes' in the keyword sequencing when you delete data from the Data table. If that's a problem then you will need an alternative design. Hope that helps. John Sidney-Woollett Anthony Best said: > I'm working on an idea that uses sequences. > > I'm going to create a table like this: > > id serial, > sequence int, > keyword varchar(32), > text text > > for every keyword there will be a uniq sequence for it eg: > > id, sequence, keyword > 1, 1, foo, ver1 > 2, 1, bar, bar ver1 > 3, 2, foo, ver2 > 4, 2, bar, bar ver2 > etc... > > I could have one sequence for all keyword which would be 1,3, etc... I > would be prefer to have them in sequence. I'm sure someone has ran into > this before, any ideas? > > Anthony. > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Sequence question
Sorry I should have added that the trigger needs to create a new keyword record if the join in the trigger fails to locate the keyword in the keyword table. Hopefully you can create the trigger yourself. The keyword table is effectively a distinct list of all keywords inserted into the data table with the associated last allocated sequence number for the keyword. John John Sidney-Woollett said: > How about using two tables; one to hold the keyword and its (last > allocated) sequence value, and the second to store your data as below. > > create table Keyword ( > keyword varchar(32), > sequence integer, > primary key(keyword) > ) > > create table Data ( > id serial, > sequence int, > keyword varchar(32), > text text > ) > > Add a trigger to the Data table for Insert so that it joins to the > (parent) keyword table and increments the keyword.sequence value, and > places that into the Data.sequence value. > > You will get 'holes' in the keyword sequencing when you delete data from > the Data table. If that's a problem then you will need an alternative > design. > > Hope that helps. > > John Sidney-Woollett > > Anthony Best said: >> I'm working on an idea that uses sequences. >> >> I'm going to create a table like this: >> >> id serial, >> sequence int, >> keyword varchar(32), >> text text >> >> for every keyword there will be a uniq sequence for it eg: >> >> id, sequence, keyword >> 1, 1, foo, ver1 >> 2, 1, bar, bar ver1 >> 3, 2, foo, ver2 >> 4, 2, bar, bar ver2 >> etc... >> >> I could have one sequence for all keyword which would be 1,3, etc... I >> would be prefer to have them in sequence. I'm sure someone has ran into >> this before, any ideas? >> >> Anthony. >> >> >> ---(end of broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] >> > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Replication
Apologies if this is a double post - I'm sure I sent a similar e-mail to the list, but it seems to have disappeared in the ether. Id there a definitive list of what replication options are currently available for 7.4 (and 7.5), and what their relative strengths and weaknesses are; ease of use, configuration, cost, support etc I need to start tackling replication of our DB in the next few weeks, and wondered what the options are. I've seen RServer and Mammoth Replication - these look good (on paper) but both appear to involve a license fee (which is more than I have to spend right now). Is pgReplication ready for 7.4 yet? And are there any other *free* options? If not maybe I'll have to find the money for a commercial solution. I'd appreciate any feedback from anyone who has a working replication solution. Thanks John Sidney-Woollett ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange permission problem regarding pg_settings
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Fri, Dec 12, 2003 at 04:24:32AM +0100, Florian G. Pflug wrote: >> Am I right to assume that updating to 7.4 will have the same effekt as >> updating to 7.3.5? > No, you're not. You can update to 7.3.5 by just dropping the new > executables into place (after stopping the postmaster, of course). I think he was asking whether 7.4 will behave the same as 7.3.5 with respect to this particular permissions issue. Which it will. You're correct that getting to 7.3.5 should be less painful (which is why I suggested it) --- but if he wants to jump to 7.4, no reason why not. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] md5 function
Hello,
Sorry for just stupid question, but I need use md5 function in 7.4
When I write:
select md5('text');
ERROR: Function md5("unknown") does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
where is problem???
thanx, miso
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Sequence name with SERIAL type
On Wed, Dec 17, 2003 at 08:59:03AM +0100, Marek Lewczuk wrote: > I'm curious if the default scheme for sequence name (which is created > with SERIAL data type) can be changed -- currently all sequences are > named like this: __seq -- can it be changed for > e.g. seq ??? You'd have to hack the source code. See the transformColumnDefinition() and makeObjectName() functions in src/backend/parser/analyze.c. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Sequence name with SERIAL type
Marek Lewczuk <[EMAIL PROTECTED]> writes: > I'm curious if the default scheme for sequence name (which is created > with SERIAL data type) can be changed -- currently all sequences are > named like this: __seq -- can it be changed for > e.g. seq ??? Sure ... just hack one or two places in the sources ... That probably wasn't the answer you wanted, but I'm quite unsure what you did want. Are you suggesting the above would be a better default naming scheme? Are you saying you want user-configurability of implicit sequence names? In either case, what's your argument why we should invest effort and possibly create backwards-compatibility issues? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] md5 function
On Wed, Dec 17, 2003 at 09:47:01AM +0100, Miso Hlavac wrote:
> Sorry for just stupid question, but I need use md5 function in 7.4
> When I write:
> select md5('text');
> ERROR: Function md5("unknown") does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
> where is problem???
Are you sure the server is 7.4? What does SELECT VERSION() show?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Sequence name with SERIAL type
On Wednesday 17 December 2003 07:59, Marek Lewczuk wrote: > I'm curious if the default scheme for sequence name (which is created > with SERIAL data type) can be changed -- currently all sequences are > named like this: __seq -- can it be changed for > e.g. seq ??? Your two options seem to be: 1. Build your own sequence and don't use SERIAL 2. Change the source (should be a simple change). -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] md5 function
On Wednesday 17 December 2003 08:47, Miso Hlavac wrote:
> Hello,
>
> Sorry for just stupid question, but I need use md5 function in 7.4
> When I write:
> select md5('text');
> ERROR: Function md5("unknown") does not exist
> Unable to identify a function that satisfies the given argument
> types You may need to add explicit typecasts
Have a look in the contrib/crypto add-on, I think md5() is in there.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] md5 function
On Wed, Dec 17, 2003 at 09:37:07AM +, Richard Huxton wrote:
> On Wednesday 17 December 2003 08:47, Miso Hlavac wrote:
> > Hello,
> >
> > Sorry for just stupid question, but I need use md5 function in 7.4
> > When I write:
> > select md5('text');
> > ERROR: Function md5("unknown") does not exist
> > Unable to identify a function that satisfies the given argument
> > types You may need to add explicit typecasts
>
> Have a look in the contrib/crypto add-on, I think md5() is in there.
md5() should be stock in 7.4.
mydb=> \x
Expanded display is on.
mydb=> \df+ md5
List of functions
-[ RECORD 1 ]---+
Result data type| text
Schema | pg_catalog
Name| md5
Argument data types | text
Owner | pgsql
Language| internal
Source code | md5_text
Description | calculates md5 hash
The internal function md5_text() is in src/backend/utils/adt/varlena.c.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequence name with SERIAL type
On 17/12/2003 07:59 Marek Lewczuk wrote:
I'm curious if the default scheme for sequence name (which is created
with SERIAL data type) can be changed -- currently all sequences are
named like this: __seq -- can it be changed for
e.g. seq ???
You could try something like
myfield integer default nextval('mysequence')
where you have previously created the sequence mysequence.
HTH
--
Paul Thomas
+--+-+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+--+-+
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] md5 function
Miso Hlavac wrote:
Hello,
Sorry for just stupid question, but I need use md5 function in 7.4
When I write:
select md5('text');
ERROR: Function md5("unknown") does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
where is problem???
I'm using 7.4 and it's working.
$ psql
Welcome to psql 7.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
db=# select md5('test');
md5
--
098f6bcd4621d373cade4e832627b4f6
(1 row)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Sequence name with SERIAL type
Tom Lane wrote: Marek Lewczuk <[EMAIL PROTECTED]> writes: I'm curious if the default scheme for sequence name (which is created with SERIAL data type) can be changed -- currently all sequences are named like this: __seq -- can it be changed for e.g. seq ??? Sure ... just hack one or two places in the sources ... That probably wasn't the answer you wanted, but I'm quite unsure what you did want. I just asked is it can be done (somehow...). Are you suggesting the above would be a better default naming scheme? Are you saying you want user-configurability of implicit sequence names? In either case, what's your argument why we should invest effort and possibly create backwards-compatibility issues? I'm not saying that proposed naming scheme is better - I think that it is more readable, and I'm using it in my project. Look at below examples: Primary key: 1. __pkey (e.g. my_clients__pkey) Foreign key: 1. fkey (e.g. my_clients__client_id__fkey) 2. _fkey (e.g. my_clients__client_id_company_id__fkey) Index: 1. index (e.g. my_clients__country__index) 2. __index (e.g. my_clients__country_city_street__index) Sequence: 1. seq (e.g. my_clients__client_id__seq) As you can see all naming schemes are very similar, and becouse of this I just wanted to know if there is something like "user-configurability implicit of sequence names". I didn't want to propose NEW naming scheme - but maybe my naming schemes are worth looking at. ML ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Firebird and PostgreSQL at the DB Corral.
[EMAIL PROTECTED] says... > > Ditto for FB. If you want to extend the code, though, you have to give > > those changes back to the community - the licence is more GPL than BSD. > maybe you need to clarify what you think of when you say "extensible". > postgresql has sql extensions like "create type"; extensibility is possible > w/o going into the source code. it sounds like you're talking about making > changes to the firebird code base here. I took the "extensible" bit from the URL that I posted http://advocacy.postgresql.org/advantages/http://advocacy.postgresql.org /advantages/ -- Extensible The source code is available to all at no charge. If your staff have a need to customise or extend PostgreSQL in any way then they are able to do so with a minimum of effort, and with no attached costs. This is complemented by the community of PostgreSQL professionals and enthusiasts around the globe that also actively extend PostgreSQL on a daily basis. -- This seemed to me to be basically a touting of the virtues of PostgreSQL being Open Source, which Firebird is also, but in a different way. FB has the "CREATE DOMAIN url VARCHAR(100)" for example. Paul... > richard -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 13:20, Christopher Murtagh wrote: > I'm trying to create a user without create privileges and I don't > seem to be able to do it. I could be clueless, but after my revoke > statements, the new user still seems to be able to create dbs, and > then have full privileges on them. Am I missing something? Oops, I meant to say that they could create tables, etc. Not dbs. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Excel, OpenOffice and Postgres
Hello javier garcia, Hello Bob; Some time ago I were using Postrges along with Excel via Microsoft Query and ODBC. I just remember that there was a problem when trying to see views but it worked with single tables. Perhaps you can experiment with that. This problem can be over come by unticking the updateable cursurs in pgsqlODBC config menu Regards Vishal Kashyap Javier El Mié 17 Dic 2003 17:01, Bob Powell escribió: Hello everyone: Can anyone tell me if it is possible to connect excel or openoffice via odbc directly to a postgres backend database. Also, if that is possible does either OpenOffice or Excel support SQL queries to said database? Bob Powell ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] restore error - language "plperlu" is not trusted
Christopher Murtagh <[EMAIL PROTECTED]> writes: > [ pg_dump script failed with ] > ERROR: language "plperlu" is not trusted > and it gave a line number, which contained the following: > GRANT ALL ON LANGUAGE plperlu TO postgres WITH GRANT OPTION; Hm. The backend flat-out rejects all attempts at GRANT on untrusted languages, even if you are superuser and the grantee is too. I'm not totally sure about the rationale for that (Peter?) but in any case pg_dump has evidently not gotten the word. If we think the backend's behavior is right then we'd better change pg_dump to suppress trying to GRANT permissions on untrusted languages. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Permissions issue?
On Wed, 17 Dec 2003, Christopher Murtagh wrote: > Greetings, > > I'm trying to create a user without create privileges and I don't seem > to be able to do it. I could be clueless, but after my revoke > statements, the new user still seems to be able to create dbs, and then > have full privileges on them. Am I missing something? > > Below is the output of my terminal window where I create a new user > (which doesn't have select privileges), but even after revoke can still > create new tables. I think you probably want to revoke create on the public schema. Create on databases controls the creation of schemas. >From the grant page: CREATE For databases, allows new schemas to be created within the database. For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres respond after toomany times to a query view
It would be good if u can give text of the view and table structure of the two tables. Some of the things u can do is. VACCUM and ANALYZE tables; Create index on coulmn specified in where clause of the query. Manoj claudia wrote: Hi, I developing a program using postgres and linux like operating system. My problem is this: I have a quite complicated view with roughly 1 record. When I execute a simple query like this "select * from myview" postgres respond after 50 - 55 minutes roughly. I hope that someone can help me with some suggestion about reason of this behavior and some solution to reduce time ti have results. Thank you for your attentions and I hope to receive some feedback as soon as possible ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [real] Re: [GENERAL] copy command problem
Bernd Helmle wrote: Don Isgitt wrote: Hi, gds2=# copy survey_match from '/home/djisgitt/perl/fixsvy.dat' with delimiter as '>'; ERROR: could not open file "/home/djisgitt/perl/fixsvy.dat" for reading: Permission denied File sysem permissions are [EMAIL PROTECTED] perl]$ ls -l /home/djisgitt/perl/fixsvy.dat -rwxrwxrwx1 djisgitt djisgitt 198441 Nov 5 12:35 /home/djisgitt/perl/fixsvy.dat Check, if your home and subdirectories has sufficient permissions: you need at least r-x for others on the specific directories, otherwise your backend is not able to access your dump. chmod o+rx /home/djisgitt should do the job, i think Bernd Thank you, Bernd; that was the problem. I obviously didn't think about the forest, since the tree was ok. :-[ Don ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote: > I think you probably want to revoke create on the public schema. Create on > databases controls the creation of schemas. > From the grant page: Hrm, thanks for the reply. I tried that too. Here's what I got (below). Am I missing something obvious? [EMAIL PROTECTED] chris]$ createuser newuser Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER [EMAIL PROTECTED] chris]$ psql chris Welcome to psql 7.4RC2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit chris=# REVOKE ALL ON SCHEMA public FROM newuser; REVOKE chris=# REVOKE ALL ON DATABASE chris FROM newuser; REVOKE chris=# \q [EMAIL PROTECTED] chris]$ psql chris -U newuser Welcome to psql 7.4RC2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit chris=> create table foo(bar integer); CREATE TABLE chris=> insert into foo values (1); INSERT 6274026 1 chris=> select * from foo; bar - 1 (1 row) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Permissions issue?
Christopher Murtagh <[EMAIL PROTECTED]> writes: > Am I missing something obvious? The permissions were granted to PUBLIC, not to newuser, and so the REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and then grant them back to whomever should have them. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] bind-dlz with postgresql driver ...
Anyone doing this? it looks simple enough, but am wondering if anyone has found any admin utilities/frontends for it, or have any 'suggested schemas' different then the examples provided ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Permissions issue?
On Wed, 17 Dec 2003, Christopher Murtagh wrote: > On Wed, 2003-12-17 at 13:54, Stephan Szabo wrote: > > I think you probably want to revoke create on the public schema. Create on > > databases controls the creation of schemas. > > From the grant page: > > Hrm, thanks for the reply. I tried that too. Here's what I got (below). > Am I missing something obvious? Ah, right, PUBLIC has rights to the public schema. You'll need to revoke those and then grant usage to newuser I believe (and correct permissions to other users as appropriate). Forgot about that. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] bind-dlz with postgresql driver ...
--On Wednesday, December 17, 2003 16:08:04 -0400 "Marc G. Fournier" <[EMAIL PROTECTED]> wrote: Anyone doing this? it looks simple enough, but am wondering if anyone has found any admin utilities/frontends for it, or have any 'suggested schemas' different then the examples provided ... I know Olivier PRENANT <[EMAIL PROTECTED]> was looking at it, but had an issue with reverse zones. You might ping him. LER Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [GENERAL] Permissions issue?
On Wed, 2003-12-17 at 15:25, Tom Lane wrote: > Christopher Murtagh <[EMAIL PROTECTED]> writes: > > Am I missing something obvious? > > The permissions were granted to PUBLIC, not to newuser, and so the > REVOKE doesn't do anything. You'd need to revoke rights from PUBLIC and > then grant them back to whomever should have them. Ahhh. that's it! I was missing something obvious. Thanks for the clue! Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] functions returning sets
Tom Lane wrote: Joe Conway <[EMAIL PROTECTED]> writes: Any guidance on the preferred fix? We cannot fix this by changing ExecScanSubPlan as you suggest. That would amount to saying that all plans have to be run to completion, which destroys LIMIT to name just one unpleasant consequence. I suspected as much. There is a mechanism available for functions to arrange to get cleanup callbacks when a containing plan is shut down early --- see RegisterExprContextCallback and ShutdownExprContext. It looks like this is not used by the existing SRF support, but I suspect it should be. OK -- I'll take a look. [ scratches head ... ] Right at the moment I don't see where ShutdownExprContext gets called during a ReScan operation. I'm quite sure it once was ... there may be another bug here ... Thanks, I'll keep that in mind too. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] functions returning sets
> Tom Lane wrote: >> [ scratches head ... ] Right at the moment I don't see where >> ShutdownExprContext gets called during a ReScan operation. I'm quite >> sure it once was ... there may be another bug here ... After further looking I've realized that memory is misserving me here; having ReScan call ShutdownExprContext was not something that ever got done. Instead I have an entry on my personal todo list that says : Need to invent ExprContextRescan and call it at appropriate places. : Knowing where all the econtexts are seems like the hard part ... though : maybe we only care about econtexts that might contain set-returning : functions, which might limit it to the targetlist... A perfectly clean solution would require being careful to reset *all* econtexts, which might be thought rather a lot of work to support a feature that's eventually going to be deprecated anyway (viz, SRFs outside of FROM). I'll see about the tlist-only case though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Excel, OpenOffice and Postgres
Try this link to OpenOffice and its postgres-sdbc-driver. http://dba.openoffice.org/drivers/postgresql/ I have Installed OO on RedHat and tested that I can connect postgres - which it does without any dramas, but I havent done much more than that. So I am not sure as to what the limit is on the complexity of the queryies asked of it can be. It doesnt connect via either odbc or jdbc, and currently it is in alpha state, which might not be what you are after. But it could be worth a look at. Cheers Matt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] restore error - language "plperlu" is not trusted
Tom Lane wrote: > Hm. The backend flat-out rejects all attempts at GRANT on untrusted > languages, even if you are superuser and the grantee is too. I'm not > totally sure about the rationale for that (Peter?) Why would you need it? It's only going to create fuss about useless functionality. > but in any case > pg_dump has evidently not gotten the word. If we think the backend's > behavior is right then we'd better change pg_dump to suppress trying > to GRANT permissions on untrusted languages. There should not be any permissions, so there should be nothing to dump. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Function Returning SETOF Problem
On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next I've googled and tried variations on the function, but without success. Can anyone help? Here's the function: CREATE TYPE place_finish AS (first NUMERIC, second NUMERIC, third NUMERIC, grandttl INTEGER, lname TEXT, fname TEXT); CREATE OR REPLACE FUNCTION updateCurrentData() RETURNS SETOF place_finish AS ' DECLARE rec RECORD; updstmt TEXT; BEGIN FOR rec IN SELECT first, second, third, grandttl, lname, fname FROM dailyList LOOP RETURN NEXT rec; updstmt := ''UPDATE currentList SET first=rec.first, second=rec.second, third=rec.third, grandttl=rec.grandttl, lname=rec.lname, fname=rec.fname WHERE lname=rec.lname AND fname=rec.fname;''; EXECUTE updstmt; END LOOP; RETURN 1; END; ' LANGUAGE 'plpgsql'; Thanks Ron ps postgres 7.4, debian stable ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Function Returning SETOF Problem
On Wed, 17 Dec 2003, Ron St-Pierre wrote: > On a daily basis I place a lot of data into the empty table dailyList, > and from that data update certain fields in currentList. I thought that > using a function would be a good way to do this(?). However I get the > following error when I run updateCurrentData(): >ERROR: set-valued function called in context that cannot accept a set >CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next > I've googled and tried variations on the function, but without success. > Can anyone help? This probably means that you're calling it like: select updateCurrentData(); and you'll need to instead call it with the function in the FROM clause, something like: select * from updateCurrentData(); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Function Returning SETOF Problem
Stephan Szabo wrote: On Wed, 17 Dec 2003, Ron St-Pierre wrote: On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next I've googled and tried variations on the function, but without success. Can anyone help? This probably means that you're calling it like: select updateCurrentData(); and you'll need to instead call it with the function in the FROM clause, something like: select * from updateCurrentData(); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] aha, that's part of it. I now get this error: ERROR: wrong record type supplied in RETURN NEXT Any ideas on this one? TIA Ron ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] restore error - language "plperlu" is not trusted
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> pg_dump has evidently not gotten the word. If we think the backend's >> behavior is right then we'd better change pg_dump to suppress trying >> to GRANT permissions on untrusted languages. > There should not be any permissions, so there should be nothing to dump. Uh, no, because you can say something like revoke all on language plperlu from public; and end up with non-null lanacl (because it instantiates the default assumption that the owner has all privileges). We could possibly hack the backend to avoid that, but I think pg_dump will need the special-case test anyway since it has to be able to cope with existing databases, wherein lanacl may be non-null. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Function Returning SETOF Problem
Ron St-Pierre <[EMAIL PROTECTED]> writes: > On a daily basis I place a lot of data into the empty table dailyList, > and from that data update certain fields in currentList. I thought that > using a function would be a good way to do this(?). However I get the > following error when I run updateCurrentData(): >ERROR: set-valued function called in context that cannot accept a set You're probably doing SELECT updateCurrentData(); where you should be doing SELECT * FROM updateCurrentData(); There are some cases where you can invoke set-valued functions in the target list rather than in the FROM list, but this isn't one of 'em. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Function Returning SETOF Problem
On Wed, 17 Dec 2003, Ron St-Pierre wrote: > Stephan Szabo wrote: > > >On Wed, 17 Dec 2003, Ron St-Pierre wrote: > > > > > > > >>On a daily basis I place a lot of data into the empty table dailyList, > >>and from that data update certain fields in currentList. I thought that > >>using a function would be a good way to do this(?). However I get the > >>following error when I run updateCurrentData(): > >> ERROR: set-valued function called in context that cannot accept a set > >> CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next > >>I've googled and tried variations on the function, but without success. > >>Can anyone help? > >> > >> > > > >This probably means that you're calling it like: > > select updateCurrentData(); > >and you'll need to instead call it with the function in the FROM clause, > >something like: > > select * from updateCurrentData(); > > > >---(end of broadcast)--- > >TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > > > > > > > > aha, that's part of it. I now get this error: >ERROR: wrong record type supplied in RETURN NEXT > Any ideas on this one? That sounds like a mismatch between the record in rec and your declared output type, but I couldn't say for sure without a complete example including the table declarations really. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Firebird and PostgreSQL at the DB Corral.
> The only reason PG views aren't > updateable by default is (AIUI) a lack of agreement on how they should > work for complex view definitions. Actually I think it's more that no one has felt like tackling it. The SQL spec only requires views to be updatable when they are "sufficiently simple", and it turns out that the spec's constraints on "sufficiently simple" eliminate all the doubtful cases. regards, tom lane Sure would be sweet to have it. While I'm here I take this chance to say.. PostgreSQL ROCKS! Thanks to everyone who makes PG great. I'll go back to lurking. G _ Get dial-up Internet access now with our best offer: 6 months @$9.95/month! http://join.msn.com/?page=dept/dialup ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] sequences not renamed with tables
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> I renamed a couple tables, and the names of their
> corresponding sequences remained the same. This causes
> a problem because when importing - the create table
> statements create sequences with different names, then
> the setval() statements use the old names. Any way to
> automatically avoid this "gotcha"?
Don't rely on the create table to make the sequences for
you: declare them yourself. Instead of this:
CREATE TABLE foobar (
id SERIAL
);
Try this:
CREATE SEQUENCE barbell_seq;
CREATE TABLE foobar (
id INTEGER NOT NULL DEFAULT nextval('barbell_seq')
);
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200312172134
-BEGIN PGP SIGNATURE-
iD8DBQE/4RJjvJuQZxSWSsgRAgEdAJ9y4eDaQENuRWVRETJdLu/vKO5PggCeKLjs
dkPyceUBRMkTbvcqMBAgLr4=
=5b5j
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Firebird and PostgreSQL at the DB Corral.
[EMAIL PROTECTED] says... > A partial index is a index on a subset of a table. The case I can think of > is a list of transactions, some of which are yet to be billed. They have a > BillID field which is NULL. since this is the recent set it is queried quite > often, so you can build an index like: > CREATE INDEX x ON table ( customerid ) WHERE billid IS NULL > Now an index can be used on customer when searching for only unbilled things > whereas normally it would also have to search for all historical things as > well. > So, you get the benefit of a smaller index that is more useful to boot. Hmmm... this is kinda like the Oracle thing where tables can be partitioned? Sort of? Paul... -- plinehan x__AT__x yahoo x__DOT__x com C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro Please do not top-post. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
