[HACKERS] Re: plpgsql.
Bill Shui wrote: >Hi there, > > I remember that in earlier versions of Postgres. > You have to do something (which I cannnot remember) to enable > a user to create plpgsql functions. > > which versions of postgres were they? > >thanks in advance. > >Bill > CREATELANG as a command ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Bug in psql tab completion
Try this: test=# create table test (a int4); CREATE test=# grant select, update on te Stop there and press 'TAB' to complete the word 'test'. Your command line then gets rewritten to : grant select, update on SET It seems that it occurs when you have commas in there... Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] plpgsql.
Hi there, I remember that in earlier versions of Postgres. You have to do something (which I cannnot remember) to enable a user to create plpgsql functions. which versions of postgres were they? thanks in advance. Bill -- The mark of a good party is that you wake up the next morning wanting to change your name and start a new life in different city. -- Vance Bourjaily, "Esquire" - Bill Shui Bioinformatics Programmer Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RE: OID wraparound (was Re: pg_depend)
"Henshall, Stuart - WCP" wrote: > > Would it be possible to offer an option for the OID column to get its value > from an int4 primary key (settable on a per table basis maybe)? > - Stuart > Sorry I don't understand well what you mean. What kind of advantages are there if we let OIDs be optional and allow such options like you offer ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [HACKERS] Re: unique index doesn't accept functions on fields
> > I'm trying create a unique index using more than one field and > > applying a function in one field to achieve case insensitive > > uniqueness but postgresql doesn't accept. > > > > create table a( > > id int primary key, > > id2 int not null, > > name varchar(50), > > unique(id2, lower(name)) > > ); > > Have you tried to just CREATE TABLE and later CREATE INDEX UNIQUE > USING... ? Postgres does not support functional indexing on multi-key indices. Chris ---(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] Does/Can PG store administrative statistics?
I'd like to have statistics on when my database was last backed up or vacuumed. Currently, I'm implementing this by using simple shell scripts that write a date stamp to ascii files. I was wondering whether this is or could be a feature added to Postgres? For example, could one of the pg_* tables contain the fields 'last_vacuum' or 'last_backup' (which would be updated every time the vacuum or pg_dump command was executed). Perhaps something like this exists that I'm unaware of? -Tony ---(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] Large queries - again...
> Tom Lane wrote: > > "Steve Howe" <[EMAIL PROTECTED]> writes: > > > ..\..\backend\lib\dllist.c(20) : fatal error C1083: Cannot open include > > > file: 'sysexits.h': No such file or directory > > > > Jan added that recently. I was wondering if it was portable or not ... > > looks like now we know :-(. > > Grmbl - tell me why I don't like Windows... Please notify me when it's fixed so that I can test it. I'll also test the error messages returned from libpq on Windows, as requested on another thread. Best Regards, Steve Howe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] plpgsql: RAISE
From: "Jan Wieck" <[EMAIL PROTECTED]> > Tom Lane wrote: > > and then the code takes care of swallowing expressions until ';', > > similarly to the way SQL commands are handled. (plpgsql's parsing > > methodology is sinfully ugly, isn't it? But I don't suppose you > > want to try to replace it...) > > It is, indeed, and I'm sorry for that. But it was the only > way I saw to make new features in the PostgreSQL main query > engine automatically available in PL/pgSQL without a single > change. Actually, I like the idea of using the SQL system to evaluate expressions - why reinvent the wheel? The only thing needed for this is a grammar for expressions so we can mix and match with RAISE a bit better. First draft doesn't look too bad - I can not deal with function-calls and brackets and still have something useful. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] plpgsql: RAISE
Tom Lane wrote: > and then the code takes care of swallowing expressions until ';', > similarly to the way SQL commands are handled. (plpgsql's parsing > methodology is sinfully ugly, isn't it? But I don't suppose you > want to try to replace it...) It is, indeed, and I'm sorry for that. But it was the only way I saw to make new features in the PostgreSQL main query engine automatically available in PL/pgSQL without a single change. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Question about porting the PostgreSQL
We are evaluating PostgreSQL for a possible port to our proprietary hardware platform. The hardware is a very high end (processing power, I/O throughput, storage capacity) storage system, attached to a host machine running Windows2K. The question is what's the right way to do it. The following is a brief description of what we think could be done, we would like to know your opinion about whether we are on the right track. The plan is to extend PostgresSql with data access functions to be executed on the storage hardware. Most of the backend code would be running on the host machine under Win2K, but user data queries would be dispatched to the storage system, where the user tables will be searched and then the results will be returned to the host. On the host, most the PostGreSQL will run unchanged, including the front end, the backend servers: the parser, planner, catalog, and the executor. The existing heapam interface is still used to access system tables. The system tables will be stored and accessed using the existing storage functions from files into the host machine memory and accessed through the buffer cache on the host machine. For user tables, the plan is to modify all the components that call heapam interface (mainly Command and Executor) for user data to call a new 'extended heapam', which basically has the same interface of the heapam but will send the request to the storage system. Page/record locking will also be changed to call the extended heapam. We would like to get your feedback about this aproach - are we on the right track or is it a waste of time? Hsin H. Lee Pyxsys Corporation 142 Q North Road Sudbury, MA 01776 Tel: 978-371-9115 ext. 116 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Tom, What you are describing is a pseudo circular log. Other database systems (such as DB2) support the concept of both circular and recoverable logs. Recoverable is named this way because recoverable logs can be used in point-in-time recovery. Both methods support crash recovery. In general, a user defines the number of log extents to be used in the log cycle. He/she also defines the number of secondary logs to use if by chance the circular log becomes full. If a secondary log extent is created, it is added to the cycle list. At a consistent shutdown, the secondary log extents are deleted. Since logs are deleted, any hope of point-in-time recovery is deleted with them. I understand your solution is for the existing architecture which does not support point-in-time recovery. If this item is picked up, your solution will become a stumbling block due the above mentioned log extent deletions. The other issues you list are of concern but are manageable with some coding. So, my question is, should PostgreSQL support both types of logging? There will be databases where you require the ability to perform point-in-time recovery. Conversely, there will be databases where an overwritten log extent (as you describe) is acceptable. I think it would be useful to be able to define which logging method you require for a database. This way, you incur the I/O hit only when forward recovery is a requirement. Thoughts/comments? Cheer, Patrick Tom Lane wrote: > > I have noticed that a large fraction of the I/O done by 7.1 is > associated with initializing new segments of the WAL log for use. > (We have to physically fill each segment with zeroes to ensure that > the system has actually allocated a whole 16MB to it; otherwise we > fall victim to the "hole-saving" allocation technique of most Unix > filesystems.) I just had an idea about how to avoid this cost: > why not recycle old log segments? At the point where the code > currently deletes a no-longer-needed segment, just rename it to > become the next created-in-advance segment. > > With this approach, shortly after installation the system would converge > to a steady state with a constant number of WAL segments (basically > CHECKPOINT_SEGMENTS + WAL_FILES + 1, maybe one or two more if load is > really high). So, in addition to eliminating initialization writes, > we would also reduce the metadata traffic (inode and indirect blocks) > to a very low level. That has to be good both for performance and for > improving the odds that the WAL files will survive a system crash. > > The sole disadvantage I can see to this approach is that a recycled > segment would not contain zeroes, but valid WAL records. We'd need > to take care that in a recovery situation, we not mistake old records > beyond the last one we actually wrote for new records we should redo. > While checking the xl_prev back-pointers in each record should be > sufficient to detect this, I'd feel more comfortable if we extended > the XLogPageHeader record to contain the file/segment number that it > belongs to. This'd cost an extra 8 bytes per 8K XLOG page, which seems > worth it to me. > > Another issue is whether the recycling logic should be "always recycle" > (hence number of extant WAL segments will never decrease), or should > it be more like "recycle if there are fewer than WAL_FILES advance > segments, else delete". If we were supporting WAL-based UNDO then I > think it'd have to be the latter, so that we could reduce the WAL usage > from a peak created by a long-running transaction. But with the present > logic that the WAL log is truncated after each checkpoint, I think it'd > be better just to never delete. Otherwise, the behavior is likely to > be that the system varies between N and N+1 extant segments due to > roundoff effects (ie, depending on just where you are in the current > segment when a checkpoint happens). That's exactly what we do not want. > > A possible answer is "recycle if there are fewer than WAL_FILES + SLOP > advance files, else delete", where SLOP is (say) about three or four > segments. That would avoid unwanted oscillations in the number of > extant files, while still allowing decrease from a peak for UNDO. > > Comments, better ideas? > > regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Fw: Leaking Handles in Postgres 7.1.2 on Cygwin dll 1.3.2 on Win 2000
- Original Message - From: eCommerce Software Solutions Inc. To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Saturday, July 21, 2001 9:15 PM Subject: Leaking Handles in Postgres 7.1.2 on Cygwin dll 1.3.2 on Win 2000 The situation is this:I have cygwin with ( dll 1.3.2 ) and latest Postgresql 7.1.2 on Win 2000with SP1.I use Java and JDBC to connect from a Windows to Postgresql server to do avery simple select:select count(*) from table1; // returns count of 2It works fine.Now I run this on 10 threads in my Java program. Each thread loops for1 times.When I do this every thing appears to be ok at first. Then, I realize that Iam loosing free memory really fast.I go in the "task manager" in windows 2000 and look at the memory usage foreach process. It is fine i.e not growing.But Available physical memory is going down really fast. I have no clue atfirst.Then I notice that in Performance tab of Windows task manager, under Totals,the handles is running very fast.I discovered that it begins from 4080 and goes on incrimenting ( to a verylarge number ) until I run out of memory.Since both client and DB server are on the same machine it is hard to tellwhich is leaking handles!Now I moved the client to another machine. The client uses JDBC to connectto the PG Database running in Win2000 Cygwin environment on another Machine.I looked at the Windows Task Monitor to notice that there are no leakinghandles on the client Machine. Therefore leak is not in my Program.The handles are being leaked by PG on the Machine acting as DB Server inCygwin environment.I hope this isolates the problem further to PG and Cygwin and not JDBC andClient code.Lets fix this problem.Thanks,Vinay
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Tom Lane wrote: > > Patrick Macdonald <[EMAIL PROTECTED]> writes: > > I understand your solution is for the existing architecture which does > > not support point-in-time recovery. If this item is picked up, your > > solution will become a stumbling block due the above mentioned log > > extent deletions. > > Hmm, I don't see why it's a stumbling block. There is a notion in the > present code that log segments might be moved someplace else for > archiving (rather than just be deleted), and I wasn't planning on > eliminating that option. I think however that a realistic archival > mechanism would not simply keep the log segments verbatim. It could > drop the page images, for a huge space savings, and perhaps also > eliminate records from aborted transactions. So in reality one could > still expect to recycle the log segments, just with a somewhat longer > cycle time --- ie, after the archiver is done copying a segment, then > you rename it into place as a forward file. Well, notion and actual practice can be mutually exclusive. Your initial message stated that you would like to rename the log segment. This insinuated that the log segment was not moved. Therefore, a straight rename would cause problems with the future point-in-time recovery item (ie. the only existing version of log segment N has been renamed to N+5). A backup of the database could not roll forward through this name change as stated. That was my objection. > In any case, a two-or-three-line change is hardly likely to create much > of an obstacle to PIT recovery, compared to some of the more fundamental > aspects of the existing WAL design (like its need to start from a > complete physical copy of the database files). So I'm not sure why > you're objecting on these grounds. Hmmm, stating that it is less of a problem than others doesn't make it the right thing to do. If the two or three lines you mention renames a segment I want to roll forward through, that's a problem. Yeah, I know it's not a problem now but it'll have to be changed when PIT comes into play. You didn't comment on the idea of two logging methods... circular and recoverable. Any thoughts? Cheers, Patrick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [BUGS] libpgtcl doesn't use UTF encoding of TCL
On Sun, Jul 22, 2001 at 08:10:32PM +0900, Tatsuo Ishii wrote: > Hum. Why don't you enable --enable-multibyte and > --enable-unicode-conversion and set client_encoding to UNICODE? That > would do a conversion from/to UTF-8 for Tcl 8.x (x > 9) clients? You're right. Probably, this way correct enough too :-) Thank you for suggest. But, I think, patching the libpgtcl has not to be superfluous. -- Eugene Faukin SOLVO Ltd. Company ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] unique index doesn't accept functions on fields
I'm trying create a unique index using more than one field and applying a function in one field to achieve case insensitive uniqueness but postgresql doesn't accept. create table a( id int primary key, id2 int not null, name varchar(50), unique(id2, lower(name)) ); Anyone have an idea ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: OID wraparound (was Re: pg_depend)
[EMAIL PROTECTED] (Lamar Owen) wrote in message news:<[EMAIL PROTECTED]>... > [trimmed cc:list] > On Wednesday 18 July 2001 17:09, Bruce Momjian wrote: > > OK, we need to vote on whether Oid's are optional, and whether we can > > have them not created by default. > > [All the below IMHO] > > OID's should be optional. yep. we don't depend upon oids > 32 bits. that's pretty standard practice for serious db apps. however, tx limit is a real problem. my vote is for solving the tx limit before chaning the oid problem. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] IDEA: Multi-master replication possible through spread (or even master-slave)...
Sean Chittenden wrote: > Has anyone here thought about using the spread libraries for WAL > replication amongst mutliple hosts? With this library I think it'd be > possible to have a multi-master replication system.. Yes, there is some work being done to use Spread as the group communication system for Postgres-R, but we are just getting started with this software. Using a group communication system to establish total order messages is one of the basic principles for synchronous multi-master replication with Postgres-R. Currently Ensemble (form Cornell University) is used, but Spread looks to be more robust and it appears to be supported on most if not all of the PostgreSQL supported platforms. It's very cool to see positive testimony for Spread, and I hope I will feel the same way as I become more familiar with it. Darren > ---(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] Large queries - again...
Tom Lane wrote: > "Steve Howe" <[EMAIL PROTECTED]> writes: > > ..\..\backend\lib\dllist.c(20) : fatal error C1083: Cannot open include > > file: 'sysexits.h': No such file or directory > > Jan added that recently. I was wondering if it was portable or not ... > looks like now we know :-(. Grmbl - tell me why I don't like Windows... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] RE: OID wraparound (was Re: pg_depend)
Would it be possible to offer an option for the OID column to get its value from an int4 primary key (settable on a per table basis maybe)? - Stuart > -Original Message- > From: Hiroshi Inoue [SMTP:[EMAIL PROTECTED]] > Sent: Saturday, July 21, 2001 7:31 AM > To: Zeugswetter Andreas SB > Cc: PostgreSQL-development > Subject: RE: OID wraparound (was Re: pg_depend) > > > -Original Message- > > Zeugswetter Andreas SB > > > > > As I mentioned already I'm implementing updatable cursors > > > in ODBC and have half done it. If OIDs would be optional > > > my trial loses its validity but I would never try another > > > implementation. > > > > But how can you do that ? The oid index is only created by > > the dba for specific tables, thus your update would do an update > > with a where restriction, that is not indexed. > > This would be darn slow, no ? > > > > Please look at my another(previous ?) posting to pgsql-hackers. > I would use both TIDs and OIDs, TIDs for fast access, OIDs > for identification. > > > How about instead selecting the primary key and one of the tid's > > (I never remember which, was it ctid ?) instead, so you can validate > > when a row changed between the select and the update ? > > > > Xmin is also available for row-versioning. But now I'm wondering > if TID/xmin are guranteed to keep such characteriscs. > Even Object IDentifier is about to lose the existence. > Probably all-purpose application mustn't use system columns > at all though I've never heard of it in other dbms-s. > > regards, > Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: Neutral Soil (OT)
August Zajonc wrote: > Perhaps Postgresql folks could start thinking of peace talk sites as well? I > recommand the tropics. Then all that's left is to find something to fight > about to justify a flight down to paradise. You are all welcome here in Cyprus. Monty too, he will find a lot of Swedish fellows here to share a beer. :-) -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: unique index doesn't accept functions on fields
Domingo Alvarez Duarte wrote: > I'm trying create a unique index using more than one field and > applying a function in one field to achieve case insensitive > uniqueness but postgresql doesn't accept. > > create table a( > id int primary key, > id2 int not null, > name varchar(50), > unique(id2, lower(name)) > ); Have you tried to just CREATE TABLE and later CREATE INDEX UNIQUE USING... ? -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Improving pg_hba.conf
> What I mean is, do host lookups first in the text file, then if a > pg_hostaccess table (example) exists, check it for host entries. This > alleviates HUP or restart etc. to reload a config file. Can't do that. Postmaster has no database access. Must be fast. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
