Re: [HACKERS] improper call to spi_printtup ???
> - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "Darko Prenosil" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Monday, June 28, 2004 9:39 PM > Subject: Re: [HACKERS] improper call to spi_printtup ??? > > > Darko Prenosil <[EMAIL PROTECTED]> writes: > > > Anyone knows what I'm doing wrong ? > > > > Well, when you didn't show us the text of the function, no. > > > > However, a reasonable bet would be that you used SPI inside the function > > and did not use it correctly, leaving the SPI state corrupted when > > control got back to plpgsql. > > You figure it out right, SPI_finish was in the wrong place. Thanks again. Regards ! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] client_min_messages in dumps?
On Tue, 29 Jun 2004, Tom Lane wrote: > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table > "foo" > is conveying any useful information? Maybe there should be another level called NOVICE :-) -- /Dennis Björklund ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] recursive SQL
seems evgen has got a sql99 compliant version of recursive SQL out and would agree to relicense it. are there any other concerns regarding evgens work? or do you want to encourage him to go this route? i have the impression he would happily join in on the postgres developement wrt recursive SQL, and some friendly words might encourage him to do the required work. cheers, jacob > -Original Message- > From: Potemkin Evgen [mailto:[EMAIL PROTECTED] > Sent: 28 June 2004 16:05 > To: jacob koehler (RRes-Roth) > Subject: Re: FW: [HACKERS] recursive SQL > > > Hello, > > yes, it would be great if patch will be included in postgres, > and if it needed sure i will relicense it to BSD. problem is > that core team don't want to include it at all. (see message > from Tom Lane on this topic). some peoples already tried to > get the patch in pg's distro, but without any result. > > the funny thing that i almost get working implementation > of SQL99 recursive queries,"just for fun";), and i think > it would not be included too;) > > regards, > evgen > -Original Message- > > >dear evgen potemkin, > > > >i hope you dont mind that i have taken this step. i saw that you > >invested some serious work, and your webpage gave me the impression > >that you are happy to share your patch. > > > >However, it seems that if you would want to make your code > part of the > >official postgres release, it also seems you would have to > license your > >patch under the same license as postgres itself, which is > not GPL, but > >BSD, see: http://www.postgresql.org/licence.html > > > >i think it would be very useful to have your patch as part of the > >official release... > > > >regards, > >jacob > > > > > >> -Original Message- > >> From: Andrew Dunstan [mailto:[EMAIL PROTECTED] > >> Sent: 26 June 2004 20:42 > >> To: [EMAIL PROTECTED] > >> Subject: Re: [HACKERS] recursive SQL > >> > >> jacob koehler (RRes-Roth) wrote: > >> > >> >hi, > >> > > >> >i am wondering what you think about including evgen > potemkin's patch > >> >for recursive SQL in the next postgres version: > >> >http://gppl.terminal.ru/ > >> > > >> >[snip] > >> > > >> > >> >- Evgen DID publish this patch under GPL, see: > >> >http://gppl.terminal.ru/README.html > >> > > >> > >> It would first have to be relicensed ... > >> > >> cheers > >> > >> andrew > >> > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Custom type with width specifier
On P, 2004-06-27 at 17:53, Tom Lane wrote: > Shachar Shemesh <[EMAIL PROTECTED]> writes: > > What do I need to do in order to get the width specifier into my type? > > Rewrite the grammar. Width modifiers are only supported on types that > are hard-wired into the grammar, mainly because they look way too much > like function calls to be distinguished without special hacking. > > Consider for example the implications of the fact that this works: > > regression=# select numeric(17,10) '1.23'; >numeric > -- > 1.23 > (1 row) > > bison has to decide *before scanning beyond the left parenthesis* > whether "numeric" is a function name or a type name. > > If you can think of a more general solution, I'm all ears, but it looks > like a hard problem that would require considerable rethinking of the > present grammar for these things. can't we make type(width) an actual function which returns another type ? this would make it even possible to add support for things like NUMBER(17,10) as an alias for NUMERIC(17,10) as an user-level addon. --- Hannu ---(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
[HACKERS] INSERT rule
Just wrote a function that takes view name as argument and generates INSERT, UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but I have trouble with INSERT rule. When inserting directly into table, default values are filled in when the field is not in the insert target list, but when using rule system field is filled with NULL. I understand that rule system rewrites the query as in CREATE RULE expression, but can I somehow detect actual attributes that are inserted, and avoid forced NULL inserts ? Tom said that current CVS support passing RECORD as an argument into the function. Is it good Idea to generate generic RULES that are simply passing NEW and OLD into some function, and try to solve updates inside that generic function ( instead of enumerating fields directly in CREATE RULE expression) ? Any suggestions ? (Sorry for bad English) Regards ! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] recursive SQL
Hi Evgen, I'm a PostgreSQL developer and I would like to see an SQL99 recursive queries feature in PostgreSQL. I'm pretty sure that Tom would be happy with an SQL99 compliant version of your patch, so long as it's up to scratch with the normal PostgreSQL coding and reliability standards. I'm not a committer myself, so I think I should explain how things work. We're unlike other projects that seem to accept almost anything that comes our way. In many ways, it's the patches that the committers reject that make PostgreSQL strong. To get your patch accepted, you have to be prepared to work WITH the committers, especially Tom, and make changes based on their feedback. I know this costs you in wounded pride (I get the same feeling sometimes), but in the end it's worth it. Sometimes the committers are very busy and it takes ages before someone actually reviews your patch. Don't take that as a snub, take it as an opportunity to keep working on it! I think that you and the PostgreSQL project can meet somewhere in the middle on this and we can get your stuff in for the 7.6 release (it's too late for 7.5 right now). Don't take this as a guarantee of course, but if you'll willing to work with us and stick with the patch, then I think it will get in. Comments? Regards, Chris -- PostgreSQL Developer jacob koehler (RRes-Roth) wrote: seems evgen has got a sql99 compliant version of recursive SQL out and would agree to relicense it. are there any other concerns regarding evgens work? or do you want to encourage him to go this route? i have the impression he would happily join in on the postgres developement wrt recursive SQL, and some friendly words might encourage him to do the required work. cheers, jacob -Original Message- From: Potemkin Evgen [mailto:[EMAIL PROTECTED] Sent: 28 June 2004 16:05 To: jacob koehler (RRes-Roth) Subject: Re: FW: [HACKERS] recursive SQL Hello, yes, it would be great if patch will be included in postgres, and if it needed sure i will relicense it to BSD. problem is that core team don't want to include it at all. (see message from Tom Lane on this topic). some peoples already tried to get the patch in pg's distro, but without any result. the funny thing that i almost get working implementation of SQL99 recursive queries,"just for fun";), and i think it would not be included too;) regards, evgen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] recursive SQL
On T, 2004-06-29 at 13:11, jacob koehler (RRes-Roth) wrote: > seems evgen has got a sql99 compliant version of recursive SQL > out and would agree to relicense it. > are there any other concerns regarding evgens work? or do you want to > encourage him to go this route? i have the impression he would happily > join in on the postgres developement wrt recursive SQL, and some friendly > words might encourage him to do the required work. > > the funny thing that i almost get working implementation > > of SQL99 recursive queries,"just for fun";), and i think > > it would not be included too;) Maybe he knows of some fundamental flaws with his implementation ? Why else does he think that it will not be accepted ? He could at least try to submit it again, once it is past the "almost" part :) BTW, I also "almost" got a working implementation of SQL99 recursive queries (at least it parsed the syntax ;), but gave up due to lack of time and also because the SQL99 recursive queries are a desceptively complex beast which I was unable to fully understand from the specs, at least the part beyond the simple parent-child tree queries. And the simple parent-child tree queriest are now doable using set-returning functions. Hannu ---(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: [HACKERS] lock timeout patch
On Tue, Jun 29, 2004 at 09:25:27 +0900, Satoshi Nagayasu <[EMAIL PROTECTED]> wrote: > > But I don't want to wait one or more minutes just for a lock. > I need to return a message to the user "retry later." or > something like that. It depends on various applications. Why not set statement timeout low when you are about to run a query that you think should return quickly? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] client_min_messages in dumps?
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > On Tue, 29 Jun 2004, Tom Lane wrote: >> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table >> "foo" >> is conveying any useful information? > Maybe there should be another level called NOVICE :-) Not a bad idea --- could satisfy everybody? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Tablespace permissions issue
Dear Tom, > Fabien COELHO <[EMAIL PROTECTED]> writes: > > It's more a "fix-me later" approach, but it does not look that bad, IMHO. > > It seems a bit risky to me. The worst possible consequence of the > ownership stuff not happening is that objects have wrong ownership (and > even there it's not so much "wrong" as "we decided we'd like this other > behavior better"). Well, if the ownership-switch transaction would fail, then I think the login would also fail and the connection would be broken... No data is lost because none where put in the database, as it has just been created. But the system would be blocked anyway. > But the consequence of not fixing reltablespace is that the database is > broken... If it is simply broken, that is it does not work at all, as it is a newly created database hency mostly empty database, it is not that bad as no data is lost. If it is broken but the fact appears much later on, that's another issue. My intuition is that a failure of such transactions would just show that there is a big underlying problem, thus having a early-on failure would be a rather good thing as it would prevent the user to go on with an instable installation. > so I'd prefer not to need to. I cannot see how it could fail under normal condition (i.e. apart disk full or hardware/os failure), but you're sure a better juge of that than me!! -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] INSERT rule
Darko Prenosil <[EMAIL PROTECTED]> writes: > Just wrote a function that takes view name as argument and generates INSERT, > UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but > I have trouble with INSERT rule. > When inserting directly into table, default values are filled in when the > field is not in the insert target list, but when using rule system field is > filled with NULL. You want to attach the defaults directly to the view, viz ALTER TABLE view ALTER COLUMN col SET DEFAULT whatever; regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] User Privileges using dblink
Hello,
we want use dblink to connect several databases in a client/server environment.
Connection from local users to the remote databases should be possible only for
privileged users. We tried a solution with dblink, embedding this command in a view,
like this:
create view inst as select * from dblink('host=pollux port=5432 dbname=cob_int
user=his_int password=#integration#', .
This solution is insecure, because login and password is readable for everyone.
We tried to call dblink without username and login, but it fails,i.e.
create view inst as select * from dblink('host=pollux port=5432 dbname=cob_int', .
What we are searching for, is a solution which uses the current login information
(user and password).
The second problem with dblink is a security hole. If you have a table without any
grants for the current user, this user can create a view to circumvent the table
privileges, i.e..
Current user is svawork (not a superuser!). Current database is sva4_int1. Table inst
has privileges only for a user sva. If user svawork tried to read from inst it fails.
This is ok.
If svawork create a view like:
create view myinst as select * from dblink('dbname=sva4_int1','select from inst')
as (...);
The view connect not to a remote database. It uses the local database.
You can read the data from table inst without any restrictions! (Select * from myinst
...)
This problem could also be resolved, if dblink uses the current login information.
Any solutions welcome.
Karsten
---(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: [HACKERS] User Privileges using dblink
Write a function that returns connection parameters (instead of hardcoding
it into view) using CURENT_USER as parameter.
create view inst as select * from dblink(
get_connection_param(CURRENT_USER) )
where get_connection_param is Your function returning text. Using system
user name is not problem at all, but password is.
You can read encrypted password from pg_shadow but only if You are a
superuser, otherwise it is another security hole...
However this is not a dblink problem, and can be summarized as:
How can I know my own password ?
I think that even server does not know Your password, it only knows
encrypted presentation (someone else could know this better).
Regards !
- Original Message -
From: "Kreißl, Karsten" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, June 22, 2004 11:50 AM
Subject: [HACKERS] User Privileges using dblink
Hello,
we want use dblink to connect several databases in a client/server
environment. Connection from local users to the remote databases should be
possible only for privileged users. We tried a solution with dblink,
embedding this command in a view, like this:
create view inst as select * from dblink('host=pollux port=5432
dbname=cob_int user=his_int password=#integration#', .
This solution is insecure, because login and password is readable for
everyone.
We tried to call dblink without username and login, but it fails,i.e.
create view inst as select * from dblink('host=pollux port=5432
dbname=cob_int', .
What we are searching for, is a solution which uses the current login
information (user and password).
The second problem with dblink is a security hole. If you have a table
without any grants for the current user, this user can create a view to
circumvent the table privileges, i.e..
Current user is svawork (not a superuser!). Current database is sva4_int1.
Table inst has privileges only for a user sva. If user svawork tried to read
from inst it fails. This is ok.
If svawork create a view like:
create view myinst as select * from dblink('dbname=sva4_int1','select
from inst') as (...);
The view connect not to a remote database. It uses the local database.
You can read the data from table inst without any restrictions! (Select *
from myinst ...)
This problem could also be resolved, if dblink uses the current login
information.
Any solutions welcome.
Karsten
---(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
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] lock timeout patch
Tom, > I'd accept a mechanism to enforce a timeout at the lock level if you > could show me a convincing use-case for lock timeouts instead of > statement timeouts, but I don't believe there is one. I think this > proposal is a solution in search of a problem. Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? I'm reluctant to go over old ground repeatedly. Let me say for myself that I would use this feature if it existed, but would not miss it a whole lot if the patch was rejected.Here's the idea: I have an OLAP database of regional office evaluations (in SQL Server, sadly) which requires that the evaluations, sometimes interlocking, of regions be "closed" simultaneously (in one transaction). This means that during the closure process, certain kinds of data entry needs to be frozen out. I am using SQL Server's lock timeout functionality for this; bascially, the data entry waits for 30 seconds, and then tells the user to try again in 10 minutes. I could do the same thing in PostgreSQL using NOWAIT and a loop on the client side. But the lock timeout is somewhat easier. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] INSERT rule
God, that was so obvious ! Thanks (again). Regards ! - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Darko Prenosil" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, June 29, 2004 4:18 PM Subject: Re: [HACKERS] INSERT rule > Darko Prenosil <[EMAIL PROTECTED]> writes: > > Just wrote a function that takes view name as argument and generates INSERT, > > UPDATE and DELETE rules for that view. It is working OK (thanks to Tom), but > > I have trouble with INSERT rule. > > When inserting directly into table, default values are filled in when the > > field is not in the insert target list, but when using rule system field is > > filled with NULL. > > You want to attach the defaults directly to the view, viz > ALTER TABLE view ALTER COLUMN col SET DEFAULT whatever; > > regards, tom lane > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] User Privileges using dblink
>
> create view myinst as select * from dblink('dbname=sva4_int1','select
> from inst') as (...);
>
> The view connect not to a remote database. It uses the local database.
> You can read the data from table inst without any restrictions! (Select *
> from myinst ...)
> This problem could also be resolved, if dblink uses the current login
> information.
I'm sorry but I forgot to comment on this. Isn't this because Your
configuration alows trusted connections for localhost ?
This is the part of pg_hba.conf comment:
# Put your actual configuration here
# --
#
# CAUTION: The default configuration allows any local user to connect
# using any PostgreSQL user name, including the superuser, over either
# Unix-domain sockets or TCP/IP. If you are on a multiple-user
# machine, the default configuration is probably too liberal for you.
# Change it to use something other than "trust" authentication.
#
# If you want to allow non-local connections, you need to add more
# "host" records. Also, remember TCP/IP connections are only enabled
# if you enable "tcpip_socket" in postgresql.conf.
Regards !
---(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
[HACKERS] Accessing Specific Schemas
I'm having trouble accessing specific schemas and wonder if maybe I haven't installed something properly in 7.4.2. Here is what is happening: SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; ERROR: parser: parse error at or near "." This error appears when I try to access ANY specific schema. Help! Kenny Cason The Boeing Company [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] INSERT rule
I do not have it here(at home), but I can send it tomorrow from work. This is first implementation and have some restrictions: 1.) all tables that are updated from view must have primary key field included into view. 2.) primary key fields can't be updated directly from view. 3.) primary key for each table must be a single field key This is because it is only way to build WHERE clause (the only way I know). Function generates rules for all fields it can update, and the rest is ignored (for example fields that are results of some function, or fields from table that has no primary key field included into view). I do not know a good way to avoid those restrictions :-( It requires some more work (one part is written using pl/pgsql, but I would like to rewrite it in C) According to Tom, 7.5 can pass record reference into function, and this opens some more possibilities. Maybe it would be even possible to write single generic function that can update any view. Regards ! - Original Message - From: "Jonathan Gardner" <[EMAIL PROTECTED]> To: "Darko Prenosil" <[EMAIL PROTECTED]> Sent: Tuesday, June 29, 2004 8:22 PM Subject: Re: [HACKERS] INSERT rule > On Tuesday 29 June 2004 03:51 am, Darko Prenosil wrote: > > Just wrote a function that takes view name as argument and generates > > INSERT, UPDATE and DELETE rules for that view. It is working OK (thanks > > to Tom), but I have trouble with INSERT rule. > > When inserting directly into table, default values are filled in when the > > field is not in the insert target list, but when using rule system field > > is filled with NULL. I understand that rule system rewrites the query as > > in CREATE RULE expression, but can I somehow detect actual attributes > > that are inserted, and avoid forced NULL inserts ? > > Tom said that current CVS support passing RECORD as an argument into the > > function. Is it good Idea to generate generic RULES that are simply > > passing NEW and OLD into some function, and try to solve updates inside > > that generic function ( instead of enumerating fields directly in CREATE > > RULE expression) ? > > > > I am very interested in your code. Where can I find it? I would like to use > it for materialized views. > > -- > Jonathan Gardner > [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Accessing Specific Schemas
PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0.3.2-7) (1 row) Hmmm... Makes me think I'm accessing the wrong version of PostgreSQL. Does that sound likely? -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 29, 2004 1:19 PM To: Cason, Kenny Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Accessing Specific Schemas On Tue, Jun 29, 2004 at 12:07:30PM -0700, Cason, Kenny wrote: > I'm having trouble accessing specific schemas and wonder if maybe I > haven't installed something properly in 7.4.2. Here is what is > happening: > > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; > > ERROR: parser: parse error at or near "." Looks like your server doesn't have schema support at all ... what does "select version()" give you? -- Alvaro Herrera () www.google.com: interfaz de línea de comando para la web. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Accessing Specific Schemas
"Cason, Kenny" <[EMAIL PROTECTED]> writes: > I'm having trouble accessing specific schemas and wonder if maybe I > haven't installed something properly in 7.4.2. Here is what is > happening: > > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; > > ERROR: parser: parse error at or near "." > > This error appears when I try to access ANY specific schema. Help! Sounds like you're talking to an older server that doesn't understand schemas. What does "select version()" return? -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] lock timeout patch
On Tue, 2004-06-29 at 18:36, Josh Berkus wrote: > Tom, > > > I'd accept a mechanism to enforce a timeout at the lock level if you > > could show me a convincing use-case for lock timeouts instead of > > statement timeouts, but I don't believe there is one. I think this > > proposal is a solution in search of a problem. > > Hmmm ... didn't we argue this out with NOWAIT? What did we conclude then? > I'm reluctant to go over old ground repeatedly. > > Let me say for myself that I would use this feature if it existed, but would > not miss it a whole lot if the patch was rejected.Here's the idea: > Can't vouch for the patch, but I can say this would get used... > I have an ... database ... > which requires that the evaluations, sometimes interlocking, of regions be > "closed" simultaneously (in one transaction). This means that during the > closure process, certain kinds of data entry needs to be frozen out. I am > using ... lock timeout functionality for this; bascially, the data > entry waits for 30 seconds, and then tells the user to try again in 10 > minutes. Just implementing this same scenario, using DB2 (...). Of course, if I had MVCC on that application, I could argue that this is not required...is that the basis of the "not required" view? > > I could do the same thing in PostgreSQL using NOWAIT and a loop on the client > side. But the lock timeout is somewhat easier. SQLServer and DB2 support a lock timeout system wide, simple but not granular. Oracle supports the NOWAIT option, even though it supports readers-dont-block locking. I prefer the NOWAIT option as it gives a more detailed handle on the exact statements that you wish to wait, or not. Without NOWAIT, we would need to set lock_timeout = 30 (seconds) Statement level timeout is a different thing entirely, since there are very often statements that need to run for 2-3 hours (even more in some cases), so statement level timeout is set to 1 (seconds). Best Regards, Simon Riggs ---(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: [HACKERS] Accessing Specific Schemas
On Tue, Jun 29, 2004 at 12:07:30PM -0700, Cason, Kenny wrote: > I'm having trouble accessing specific schemas and wonder if maybe I > haven't installed something properly in 7.4.2. Here is what is > happening: > > SELECT * FROM INFORMATION_SCHEMA.SCHEMATA; > > ERROR: parser: parse error at or near "." Looks like your server doesn't have schema support at all ... what does "select version()" give you? -- Alvaro Herrera () www.google.com: interfaz de línea de comando para la web. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Default libpq service
A while ago it was speculated that it might be nice to have a default service in libpq's pg_service.conf file that would supply missing connection parameters if none are specified elsewhere, so users could, say, set the default server host in a configuration file instead of environment variables. The precendence would be like this: explicit specification, service (if explicitly specified), environment, (new: ) default service, compiled-in default (e.g., for port). (Or maybe the default service before the environment?) Comments? Better ideas? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bounce messages
Could some kind person please contact [EMAIL PROTECTED] and ask him to fix the filters that generate bounces from things I send to PG mailing lists? My attempts have failed. I get this: ... while talking to strader.xs4all.nl: <<< 550 5.7.1 <[EMAIL PROTECTED]>... Please be informed that you are currently blocked from sending email to this address. - If you feel this is wrong, send an email to <[EMAIL PROTECTED]>. - After we have received this email, you will be able to send email messages to this address again. - You are receiving this error because we try to eliminate spam from our site. - Sorry for the inconvenience! I tried that and all I got was another bounce. Thanks for any help andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] lock timeout patch
> Tom, > > > I'd accept a mechanism to enforce a timeout at the lock level if you > > could show me a convincing use-case for lock timeouts instead of > > statement timeouts, but I don't believe there is one. I think this > > proposal is a solution in search of a problem. > > Hmmm ... didn't we argue this out with NOWAIT? What did we conclude > then? > I'm reluctant to go over old ground repeatedly. The result of this debate was that there was some use for it. NOWAIT is now implemented for table locking but not for row locking. Personally I think there is some use for forcing transactions to abort as soon as a lock situation is detected (although I probably wouldn't use it). For row level locking I would suggest to the original poster to compare xmin/xmax (check the docs) to pre check the row level lock condition. This is inelegant but it mostly works. FWIW, I think the treatment of locking in the docs could use some improvement. Especially wrt MVCC and pessimistic locking and the 'big picture' issues going on there (especially why the former is better than the latter). Merlin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Default libpq service
On Tue, Jun 29, 2004 at 09:46:34PM +0200, Peter Eisentraut wrote: > A while ago it was speculated that it might be nice to have a default > service in libpq's pg_service.conf file that would supply missing > connection parameters if none are specified elsewhere, so users could, > say, set the default server host in a configuration file instead of > environment variables. > > The precendence would be like this: explicit specification, service (if > explicitly specified), environment, (new: ) default service, > compiled-in default (e.g., for port). (Or maybe the default service > before the environment?) > > Comments? Better ideas? Only problem that springs to mind is that you probably wouldn't want a default password in a world-readable /etc/ file, but you can't stop a determined fool anyway. I know it would make my life a little easier, so yeah, go for it. This was proposed for libpqxx a long time ago, but I felt it belonged more at the libpq level. Jeroen PS - Nette Witmung, danke :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Availability Options in 7.5 - PITR & Replication
I'm rounding up last bits of Archive Recovery work now... One of these is to give my humble opinion of where Archive Recovery fits into the mix of options for PostgreSQL. This is effectively a "positioning" of the high availability options. Firstly, my experience comes from larger enterprises that have up to now used Oracle and Db2 almost exclusively, with many new SQL Server systems springing up and possibly a Teradata data warehouse as well. Looking at, say, Oracle, we have these options: - Crash Recovery - Archive Recovery (in 6) - Automated Standby Database(hand-coded in 7, an option in 8+) - Replication (worked in 8+) - Shared-Cache Clustering (only really worked from 9i+) PostgreSQL now also sports these options: - Crash Recovery(in 7.x already) - Archive Recovery (subject to approval, in 7.5) - Automated Standby Database(now possible, subject to approval) - Replication (Slony-I) ...and so PostgreSQL has an almost matching set of options now, with an almost matching development path. The PostgreSQL options: Where do they fit? Taking the last two options: - Replication (Slony-I, etc) This, I regard, as the premier Active-Active High Availability solution, when you-absolutely-gotta-have-high-availability and can spend the time to make sure its all working. [I have nothing bad to say about this, just that it is overkill for some] - Automated Standby Database(now possible, subject to approval) ASD provides Active-Passive support. This is a simpler, very low overhead mechanism for systems with lower availability requirements, but where recovery time would be an issue. This is a frequent choice for sites that do not wish to invest in complex backup software or tape units etc.. This clearly not required when replication is in use. ...and then discussing... - Archive Recovery provides a safer environment for important data, since changes can be rolled forward through all kinds of system change, and allows you to re-coordinate integrated systems when one (maybe not even you..) crashes. This is regarded by most enterprises as the bottom line entry point for trustworthy data servers, whatever they spend on hardware replication, RAID or other stuff. All sites I have worked with have used log archiving as well as Replication, when replication is used. My thinking is that the full spread of options is as important as any one option. Jan and his teams work is an important flagship for us all. I welcome your comments and insight, whilst I neaten up the code.. Best regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Accessing Specific Schemas
On Tue, Jun 29, 2004 at 01:24:05PM -0700, Cason, Kenny wrote: > PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red > Hat Linux 8.0.3.2-7) > (1 row) > > Hmmm... Makes me think I'm accessing the wrong version of PostgreSQL. Does that > sound likely? Certainly ... at least this is not the 7.4.2 you just installed. > On Tue, Jun 29, 2004 at 12:07:30PM -0700, Cason, Kenny wrote: > > I'm having trouble accessing specific schemas and wonder if maybe I > > haven't installed something properly in 7.4.2. Here is what is > > happening: -- Alvaro Herrera () "El día que dejes de cambiar dejarás de vivir" ---(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: [HACKERS] [PATCHES] nested xacts and phantom Xids
Alvaro Herrera <[EMAIL PROTECTED]> writes: > As with the bufmgr.c original patch, I don't really know how to test > that this actually works. I fooled around with printing what it was > doing during a subtrans commit/abort, and it seems OK, but that's about > it. In what situations can a transaction roll back with a nonzero > reference count in a local buffer? You need an active cursor, eg begin; declare c cursor for select * from tenk1; fetch 1 in c; ... now you've got an open buffer refcount to some page of tenk1 I forgot to mention to you that that code didn't work at all, btw. I have fixed some of the problems in my local version but there's still a fairly large issue, which is what exactly we think the semantics of a cursor declared in a subtransaction ought to be. With bufmgr set up to consider open reference counts as a bug, we cannot hold such a cursor open past subtrans commit. One possible approach is to consider subxact commit the same as main xact commit as far as cursors are concerned: materialize anything declared WITH HOLD, close anything declared without. The other theory we could adopt is that cursors stay open till main xact commit; this would imply not releasing buffer refcounts at subxact commit, plus any other resources needed by the cursor. We're already holding locks that way and it probably wouldn't be a big change to make bufmgr work the same. I'm not sure that there are any other resources involved, other than the Portal memory which we already handle properly. The first approach is a lower-risk path; I'm not sure if the second one might have some hidden gotchas. It seems like the second one would be more flexible though. Any opinions which to pursue? Oh, there's another point: what happens if an outer xact level declares a cursor, which is then FETCHed from by a subtransaction? At minimum we have the problem that this could change the set of buffer pins held, which breaks the present bufmgr solution entirely. It gets even more interesting if you are of the opinion that subtransaction failure should cause the effects of the FETCH to be undone --- we have no way to do that at all, because there's no mechanism for saving/restoring the state of an entire execution plan tree. We might have to prohibit subtransactions from touching outer-level cursors, at least for 7.5. This would in turn make it a bit questionable whether there's any point in letting cursors propagate up out of subtransactions... regards, tom lane ---(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: [HACKERS] bounce messages
removed from lists ... On Tue, 29 Jun 2004, Andrew Dunstan wrote: Could some kind person please contact [EMAIL PROTECTED] and ask him to fix the filters that generate bounces from things I send to PG mailing lists? My attempts have failed. I get this: ... while talking to strader.xs4all.nl: <<< 550 5.7.1 <[EMAIL PROTECTED]>... Please be informed that you are currently blocked from sending email to this address. - If you feel this is wrong, send an email to <[EMAIL PROTECTED]>. - After we have received this email, you will be able to send email messages to this address again. - You are receiving this error because we try to eliminate spam from our site. - Sorry for the inconvenience! I tried that and all I got was another bounce. Thanks for any help andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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: [HACKERS] [PATCHES] nested xacts and phantom Xids
On Tue, Jun 29, 2004 at 06:59:20PM -0400, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > As with the bufmgr.c original patch, I don't really know how to test > > that this actually works. [...] > > I forgot to mention to you that that code didn't work at all, btw. Bad news, I guess. > The other theory we could adopt is that cursors stay open till main xact > commit; this would imply not releasing buffer refcounts at subxact > commit, plus any other resources needed by the cursor. We're already > holding locks that way and it probably wouldn't be a big change to make > bufmgr work the same. I'm not sure that there are any other resources > involved, other than the Portal memory which we already handle properly. Well, AFAIR originally I had thought that refcounts should be held at subtrans commit; you suggested that there was no reason for a subtrans to keep a buffer refcount and that was it. I think the open cursor is a good reason why the count should be kept; it appears less useful if you can't use the cursor anywhere out of the level that created it. > Oh, there's another point: what happens if an outer xact level declares > a cursor, which is then FETCHed from by a subtransaction? At minimum we > have the problem that this could change the set of buffer pins held, > which breaks the present bufmgr solution entirely. It gets even more > interesting if you are of the opinion that subtransaction failure should > cause the effects of the FETCH to be undone --- we have no way to do > that at all, because there's no mechanism for saving/restoring the state > of an entire execution plan tree. Hmm ... yes, this could be very ugly indeed, but I haven't even looked at the executor code so I can't comment. Are executor nodes copyable? Oh, and I've been playing with large objects and I've encountered bugs elsewhere. I'll look at it with the new patch you just posted. -- Alvaro Herrera () "Vivir y dejar de vivir son soluciones imaginarias. La existencia está en otra parte" (Andre Breton) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Accessing Specific Schemas
On Tue, 2004-06-29 at 21:24, Cason, Kenny wrote: > PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red > Hat Linux 8.0.3.2-7) > (1 row) > > > Hmmm... Makes me think I'm accessing the wrong version of PostgreSQL. Does that > sound likely? > Yes. You can get into problems if you have both an RPM version and a compiled from source version on the same machine... You may have accessed the RPM version when you thought you were accessing the hand compiled one. Red Hat normally has some stuff on there from earlier versions. To check, select all packages, filtered by "postgres" rpm -qa | grep postgres You might then want to decide to de-install the earlier version, or at least more strongly locate the one you just compiled. Best regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] unsubscribe-digest
unsubscribe-digest 诚挚问候 -- 周仁军 游戏运营二部 上海盛大网络发展有限公司 TEL:021-50504740-6124 Email:[EMAIL PROTECTED] -- www.bfo.com.cn -- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004-6-27 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] [PATCHES] nested xacts and phantom Xids
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Hmm ... yes, this could be very ugly indeed, but I haven't even looked > at the executor code so I can't comment. Are executor nodes copyable? Nope, and even if we had support for that the executor tree per se is just the tip of the iceberg. There's also indexscan status, SRF function internal state, yadda yadda. I think the odds of doing something with all that stuff for 7.5 are exactly zero ... we'd better define a stopgap behavior. > Oh, and I've been playing with large objects and I've encountered bugs > elsewhere. I'll look at it with the new patch you just posted. Wouldn't surprise me, we've not looked at that yet either. I do feel that we have enough things working that we should commit to nested transactions for 7.5. There will be some things that we have to restrict, such as cursors and perhaps large objects. But it's surely better than no subtransactions at all. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] lock timeout patch
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > FWIW, I think the treatment of locking in the docs could use some > improvement. Especially wrt MVCC and pessimistic locking and the 'big > picture' issues going on there (especially why the former is better than > the latter). Send a patch ... regards, tom lane ---(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
