[HACKERS] Win32 port
Hi. Where I can download sources of win32 port? Can I help for win32 port? Thanks. ---(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] PostgreSQL 7.4 and Microsoft's SMS
If you're talking about running as a backend for SMS you can be almost certain it will not work. That one today works only with MS SQL (not Oracle, DB2 or anybody else that already have native Win32 versions), and probably uses a *lot* of the non standard features in it. IIRC the beta of the new version of SMS coming out soon won't even work with older versions of MSSQL - only SQL Server 2000 is supported. If you mean that you can install it using the SMS Software Distribution feature, that should be possible. Almost any app can be distributed with that, assuming you repackage the setup. Almost all setup programs that supports fully-noninteractive install will work without repackaging. The *current* psql client will already install using SMS. Don't know about the ODBC driver, but it should probably be possible with that one too. //Magnus -Original Message- From: Justin Clift [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 4:40 AM To: PostgreSQL Hackers Mailing List Subject: [HACKERS] PostgreSQL 7.4 and Microsoft's SMS Hi guys, Just received a query through the Advocacy site's request form... Does anyone know if PostgreSQL 7.4's native windows version will/would be compatible with Microsoft's SMS (System Management Server)? Looks like some places will be considering it for Enterprise Deployment if it is. :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [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])
[HACKERS] createlang failed!
When I run createlang plpgsql template1 on linux 7.3 (pg version 7.3.1), it failed - createlang plpgsql template1 ERROR: stat failed on file '$libdir/plpgsql': No such file or directory createlang: language installation failed Can someone point me where could go wrong? thanks. johnl ---(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] Generate user/group sysids from a sequence?
Shridhar Daithankar [EMAIL PROTECTED] writes: I would say keep range of user specified ids and automatically generated ids exclusive to each other. No, that won't do. The principal reason why we keep the explicit SYSID option around at all is so that a DBA can deliberately recreate a user with the same SYSID he had before, in case the user is dropped by mistake leaving objects or permissions behind. So we can't just forbid conflicts completely. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] createlang failed!
John Liu [EMAIL PROTECTED] writes: When I run createlang plpgsql template1 on linux 7.3 (pg version 7.3.1), it failed - createlang plpgsql template1 ERROR: stat failed on file '$libdir/plpgsql': No such file or directory createlang: language installation failed What does 'pg_config --pkglibdir' say? Is there a plpgsql.so in there? Is there any additional information about the failure in the postmaster's log? (If you are routing the postmaster's stderr to /dev/null, now is a good time to stop doing that --- what I'm wondering about is dynamic-linker errors, which will only appear on stderr.) regards, tom lane ---(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] Generate user/group sysids from a sequence?
Shridhar Daithankar [EMAIL PROTECTED] writes:
If this is the idea, I suggest that all user/group transactions be logged. So
that admin can go thr. them to find out what was id of an user at any given
time. Otherwise admin is not likely to keep list of uids handy and in tough
situation, that is what he/she is going to need.
No, it's not really a problem. The only reason why you'd care about
recreating a user with a pre-existing SYSID is if there are still
objects or permissions entries in the database with that ownership ID
--- and in that case, you can easily see what ID they have. An example:
regression=# create table foo (f1 int);
CREATE TABLE
regression=# create user joe;
CREATE USER
regression=# grant select on foo to joe;
GRANT
regression=# \z foo
Access privileges for database regression
Schema | Table | Access privileges
+---+
public | foo | {=,postgres=arwdRxt,joe=r}
(1 row)
... time passes ...
regression=# drop user joe;
DROP USER
... after awhile you realize your mistake:
regression=# \z foo
Access privileges for database regression
Schema | Table | Access privileges
+---+
public | foo | {=,postgres=arwdRxt,123=r}
(1 row)
... and here's how you get out of it:
regression=# create user joe with sysid 123;
CREATE USER
regression=# \z foo
Access privileges for database regression
Schema | Table | Access privileges
+---+
public | foo | {=,postgres=arwdRxt,joe=r}
(1 row)
(now you can do a REVOKE if you need to)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] createlang failed!
John Liu [EMAIL PROTECTED] writes: Thanks, fixed. The problem was caused - there's a postgreSQL came with the Redhat server, but I tried to install one only used by a specific user, I set LD_LIBRARY_PATH in this user's profile. pg_config --pkglibdir points to the correct lib dir, but when I run createlang plpgsql template1 it keeps failed. I suspect that the linker is ignoring LD_LIBRARY_PATH in favor of the ldconfig path. Or it might just be that you neglected to ensure that LD_LIBRARY_PATH is actually set in the environment of the running postmaster. 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] Generate user/group sysids from a sequence?
Bruce Momjian [EMAIL PROTECTED] writes: The reason I was being cautious is to handle cases where people are poking in pg_shadow directly. If they're poking pg_shadow directly, I think it's up to them to avoid or cope with sysid conflicts (the unique indexes on the table will prevent the worst errors). ISTM it is sufficient for CREATE USER to not cause conflicts when used as documented. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Generate user/group sysids from a sequence?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: The reason I was being cautious is to handle cases where people are poking in pg_shadow directly. If they're poking pg_shadow directly, I think it's up to them to avoid or cope with sysid conflicts (the unique indexes on the table will prevent the worst errors). ISTM it is sufficient for CREATE USER to not cause conflicts when used as documented. OK. Do we have many people left upgrading from pg_dump's that COPY into pg_shadow? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Suggestion for aggregate function
I have an idea for an aggregate function (actually a pair) that would be very useful. It's something I've wanted very frequently with Oracle and other databases and while it's possible to implement in SQL it's hard to do efficiently. Whereas it would be really easy for the database to do it efficiently. lookup_min(column1,column2) lookup_max(column1,column2) would return the value of column2 (or one of the values in the case of duplicates) where column1 is the minimum/maximum value. Ie, it would have an accumulator that stores two values, the minimum/maximum value found so far, and the value of column2 for that record. So it would be possible to say for example: select min(column1),lookup_min(column1,column2) from tab to do the equivalent of: select column1,column2 where column1=(select min(column1) from tab) limit 1 except it would be way more efficient. (Especially if there's an index on column1 and postgres were taught to use indexes for min/max, but that's a different story.) I'm not sure on the names, perhaps someone has a better idea? I would be interested in doing this myself, it sounds like a fairly straightforward thing to implement and would be a useful first project. However I'm really a bit bewildered by the number of steps aggregate functions seem to have to go through to store accumulator data. It seems like they're going to a lot of effort to store the accumulator data in a database internal data-type. Is there something I can read to catch up on what these data structures are for and how to use them? -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion for aggregate function
On Fri, Jan 17, 2003 at 13:39:11 -0500, Greg Stark [EMAIL PROTECTED] wrote: So it would be possible to say for example: select min(column1),lookup_min(column1,column2) from tab to do the equivalent of: select column1,column2 where column1=(select min(column1) from tab) limit 1 except it would be way more efficient. (Especially if there's an index on column1 and postgres were taught to use indexes for min/max, but that's a different story.) The following will be more efficient than your function if there is a usable index on column1: select column1,column2 from tab order by column 1 limit 1 ---(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] PostgreSQL 7.4 and Microsoft's SMS
-Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 11:30 To: Justin Clift; PostgreSQL Hackers Mailing List Subject: Re: [HACKERS] PostgreSQL 7.4 and Microsoft's SMS The *current* psql client will already install using SMS. Don't know about the ODBC driver, but it should probably be possible with that one too. It certainly should do - that's one of the reasons it's in a Microsoft Installer database. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Suggestion for aggregate function
Bruno Wolff III [EMAIL PROTECTED] writes: On Fri, Jan 17, 2003 at 13:39:11 -0500, Greg Stark [EMAIL PROTECTED] wrote: So it would be possible to say for example: select min(column1),lookup_min(column1,column2) from tab to do the equivalent of: select column1,column2 where column1=(select min(column1) from tab) limit 1 As several people have pointed out this example isn't sufficiently complex to make rule out various other reasonably efficient SQL implementations. If you're unconvinced that this function would be handy consider a more complex query: SELECT item.*, store.*, x.lowest_price FROM item, store, ( SELECT item_id, min(price) AS lowest_price, lookup_min(price,store_id) AS lowest_price_store FROM items_for_sale WHERE item_category = ? GROUP BY item_id) AS x WHERE item.item_id = x.item_id AND store.store_id = x.store_id There's really no reason for the database to have to do more than one scan of items_for_sale with one nested_loops lookup of item and store. Ideally if there's an index on items_for_sale on item_id, price it should be able to use it too, but that's unlikely. Currently to write this I think you would have to join against items_for_sale twice, once to group by item_id and get the least price, then again to lookup the store. SELECT item_id, min(store_id) FROM items_for_sale, ( SELECT min(price) AS lowest_price FROM items_for_sale WHERE item_category = ? GROUP BY item_id ) AS x WHERE items_for_sale.item_id = x.item_id AND items_for_sale.price = x.lowest_price -- greg ---(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] v7.3.1 psql against a v7.2.x database ...
[EMAIL PROTECTED] wrote: snip I have strongly considered doing this, and even started on the project some time ago. (I've stopped now). At first I wanted to add 7.3 and 7.4 features to a 7.2 psql. Then I considered writing a master psql that could handle any backend. In the end, however, I realized that with 7.3 well out the door, it was better to encourage people to upgrade to 7.3 and spend my energies on other things. If there is still a strong interest however, I can easily help out and share what I have already done. With ever more larger businesses adopting PostgreSQL, and that leading on to more places having several versions of PostgreSQL in operation simultaneously (i.e. development vs production) we're probably going to need to give psql the ability to handle whichever version of the PG backend it happens to connect to. Marc's suggestion of breaking psql into it's own sub-project makes good sense from that point of view. So... this stuff is interesting Greg. :-) Regards and best wishes, Justin Clift -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200301161656 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+JyrzvJuQZxSWSsgRAmdUAJ4upWRFGKi1K5WYAwCVf36w1V4aAQCgvuD3 kCy+Q6EE/pum7Sojim+TJdM= =Tvn6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Win32 port (native)
Okay, I have finally extracted out a patch that applied to a 7.2.1 tree get's me something that compiles and passes all regression tests on RedHat Linux and Windows 2000. To clearify upfront, even if the build process of this port uses a few cygwin tools, the final executables and libraries do not depend on cygwin. I need to write up the 42 steps how to build. For those who want to be prepared, you'll need a unix system to patch, a win32 environment with VC++ 6.0, Tcl, bison*, flex*, sed* and touch* (*=from cygwin). To run the regression tests, one would need an MKS toolkit as it crashes the cygwin bash ... we didn't bother with those minor issues. I will post 2 patches later (meaning, probably tomorrow or latest Sunday) to the patches list. One will be all the changes to existing files (about 11,500 lines context diff), one will be the new files added. As a PostgreSQL coreteam member I want to thank my employer, the PeerDirect Corporation, for contributing this work, which IMHO is an important step for PostgreSQL. What we need from here are some ideas how this port can be lifted up to the current 7.4 development tree. There are some TODO items scattered throughout the code. But, it compiles and works, so it's a good point to start from I think. Looking at the demand for a native Win32 port I would expect some people willing to take it from there. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestion for aggregate function
On 17 Jan 2003 15:12:58 -0500, Greg Stark [EMAIL PROTECTED] wrote: SELECT item.*, store.*, x.lowest_price FROM item, store, ( SELECT item_id, min(price) AS lowest_price, lookup_min(price,store_id) AS lowest_price_store FROM items_for_sale WHERE item_category = ? GROUP BY item_id) AS x WHERE item.item_id = x.item_id AND store.store_id = x.store_id There's really no reason for the database to have to do more than one scan of items_for_sale with one nested_loops lookup of item and store. Greg, we already have this feature, just the syntax is a bit different :-) SELECT item.*, store.*, x.lowest_price FROM item, store, ( SELECT DISTINCT ON (item_id) item_id, price AS lowest_price, store_id AS lowest_price_store FROM items_for_sale WHERE item_category = ? ORDER BY item_id, price) AS x WHERE item.item_id = x.item_id AND store.store_id = x.lowest_price_store; Ideally if there's an index on items_for_sale on item_id, price it should be able to use it too, but that's unlikely. Servus Manfred ---(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] Suggestion for aggregate function
Greg Stark [EMAIL PROTECTED] writes: select min(column1),lookup_min(column1,column2) from tab One small problem is that we only support single-argument aggregates. As of 7.3 this is no longer wired into the system catalog layout, but it's still wired into various internal datastructures. Anyone interested in trying to fix it? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Options for growth
On Thursday 16 January 2003 12:23, Neil Conway wrote:
On Thu, 2003-01-16 at 11:42, D'Arcy J.M. Cain wrote:
Is [Oracle RAC] really as simple as it sounds or would we just be
giving up the other two for a new set of problems.
That's a question you should be asking to an authority on Oracle RAC
(which pgsql-hackers is not).
True but I already have their perspective. Now I am looking for reasons to
stay with PostgreSQL.
My idea is to create a new middleware layer that allows me to split
things up based on various criteria without changing my application.
Personally, I would not be very eager to use home-brew replication for a
heavy-load, production-critical application (which is what your app
sounds like). But YMMV...
Not replication per se although I suppose that could be built in. What I am
talking about is an application that knows our business logic and brokers
requests for data from the database(s) in an OO way. The idea is to split
data up in the middleware whenever necessary.
We are also looking at hardware solutions, multi-CPU PCs with tons (24GB)
of memory. I know that memory will improve access if it prevents
swapping but how well does PostgreSQL utilize multiple CPUs?
The estimates I've heard from a couple parties are that PostgreSQL tends
to scale well up to 4 CPUs. I've been meaning to take a look at
improving that, but I haven't had a chance yet...
Cool. I am looking at Tyan boards that have 4 CPUs and 24GB memory.
Another option is to put some money toward the current development
effort to get truly scalable replication for PostgreSQL. In the end, I'd
think the cost of subsidizing some of that development would be a
fraction of the license fees you'll end up paying Oracle over the
years...
This is definitely an option. I can probably put both people and money into
such an effort. I would be happy to hear from people who can work on that.
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Suggestion for aggregate function
Manfred Koizar [EMAIL PROTECTED] writes: Greg, we already have this feature, just the syntax is a bit different :-) SELECT DISTINCT ON (item_id) item_id, price AS lowest_price, store_id AS lowest_price_store FROM items_for_sale WHERE item_category = ? ORDER BY item_id, price Neat! I hadn't seen this. I would have liked to have had that feature on Oracle! (Please don't tell me I did, I went through such pains to work around not having it.) Would this query be efficient if there's an index on item_id, price ? That is, would it know to do an index scan and be able to skip to the next item_id in the index as soon as a price was found? -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Win32 port (native)
Jan Wieck wrote: As a PostgreSQL coreteam member I want to thank my employer, the PeerDirect Corporation, for contributing this work, which IMHO is an important step for PostgreSQL. Yes, a very important step. A big thank you to PeerDirect. What we need from here are some ideas how this port can be lifted up to the current 7.4 development tree. There are some TODO items scattered throughout the code. But, it compiles and works, so it's a good point to start from I think. Looking at the demand for a native Win32 port I would expect some people willing to take it from there. I have a request for this. Would it be possible to give priority to the client utilities? Apart from possibly helping in the rest of the port by facilitating dumping/restoring/testing, it would also enable those working on Windows clients with unix backends to work more easily now already. Personally, I am now using the psql that came with the beta4 of the Win32 port for everything. Even when connecting to a 7.3 server the problems (no schema support etc.) are far outweighted by the advantages of having a lightweight (just libpq.dll + psql.exe, no cygwin, no installation) client tool. But it would be nice if schema support etc. was available and with the exception of pg_dump I am personally not very concerned about using beta client utilities (while I would have reservations about using a beta database server). Jochem ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Win32 port (native)
Jochem van Dieten wrote: everything. Even when connecting to a 7.3 server the problems (no schema support etc.) are far outweighted by the advantages of having a lightweight (just libpq.dll + psql.exe, no cygwin, no installation) client tool. But it would be nice if schema support etc. was available You can already compile libpq and psql on win32; see: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/install-win32.html Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Options for growth
On Thursday 16 January 2003 20:54, Christopher Kings-Lynne wrote:
toss PostgreSQL in favour of Oracle with Remote Access Cluster (RAC)
software.
You mean Real Application Clusters?
Oops, yes.
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Options for growth
On Thursday 16 January 2003 11:59, Adrian 'Dagurashibanipal' von Bidder wrote:
On Thu, 2003-01-16 at 17:42, D'Arcy J.M. Cain wrote:
We are also looking at hardware solutions, multi-CPU PCs with tons (24GB)
of memory. I know that memory will improve access if it prevents
swapping but how well does PostgreSQL utilize multiple CPUs?
At most one CPU is used for any single postgres backend (that means for
any single database connection). So, if your load problem is single
queries being too slow, thee's nothing you can do with adding more CPUs.
If your problem is many connections maxing out the db, PostgreSQL can
take full advantage of multiple CPUs.
I most definitely have multiple queries running at once. My main issue is
whether PostgreSQL scales up properly or does it get bogged down with too
many locked queries.
Of course, most db apps still are not cpu bound, so you'd have to do
some careful benchmarking first or you'll be spending too much money.
Natch.
--
D'Arcy J.M. Cain darcy@{druid|vex}.net | Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
---(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] Can we revisit the thought of PostgreSQL 7.2.4?
On Thursday 16 January 2003 22:47, Justin Clift wrote: Although we haven't wanted to release a 7.2.4, and have instead encouraged people to upgrade to 7.3.x, there are places out there who's applications aren't compatible with 7.3.x and would also need to upgrade them as well. Incidentally, has anyone else noticed the security update onslaught from Red Hat for older PostgreSQL versions? They even backported the fixes to 6.5.3 from Red Hat 6.2 (as well as for 7.0 and 7.1 as released in the respective Red Hat Linux versions). Should I forward that notice here? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can we revisit the thought of PostgreSQL 7.2.4?
Lamar Owen [EMAIL PROTECTED] writes: Incidentally, has anyone else noticed the security update onslaught from Red Hat for older PostgreSQL versions? They even backported the fixes to 6.5.3 from Red Hat 6.2 (as well as for 7.0 and 7.1 as released in the respective Red Hat Linux versions). Should I forward that notice here? Some of the guys in Toronto got excited about it, but I can't see a lot of value there myself. If you're still running 6.5.3, is it likely you notice updates from anywhere? Red Hat 6.2 is still nominally supported (until March 31, it says here) so I suppose there's a corporate compulsion to back-patch anything that's labeled a security issue. But let's get real ... PG 6.anything is stone-age code now. regards, tom lane Red Hat Database project PS: I'm not taking a position on Justin's suggestion that there should be a 7.2.4. Marc and Bruce would be the ones who have to do the work, so they get to make the decision... ---(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] Generate user/group sysids from a sequence?
Tom Lane wrote: Currently, the default sysid assigned to a user or group is computed as max(sysid)+1. We've seen a couple of complaints now from people who deleted their newest user, made another user, and found that permissions from the deleted user carried over to the new one. It seems to me that the easiest solution to this is to generate the default sysid from a sequence object, instead. Unless someone deliberately resets the sequence, there'd be no conflicts. A small difficulty is that explicitly-specified sysids could conflict with sysids generated later by the sequence. We could perhaps fix this by forcing up the sequence setting to be at least as large as an explicitly-given ID (compare the handling of explicitly loaded OIDs). A sequence sounds like a good idea. When we create a user, we can use MAX() to find the maximum, and if that is less than the sequence value, bump up the sequence to equal max and add the row, again incrementing the sequence. Another idea would be to put a trigger on the column so that any INSERT/UPDATE would automatically bump up the sequence with setval(). The reason I was being cautious is to handle cases where people are poking in pg_shadow directly. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] v7.3.1 psql against a v7.2.x database ...
With ever more larger businesses adopting PostgreSQL, and that leading on to more places having several versions of PostgreSQL in operation simultaneously (i.e. development vs production) we're probably going to need to give psql the ability to handle whichever version of the PG backend it happens to connect to. Marc's suggestion of breaking psql into it's own sub-project makes good sense from that point of view. Subproject or not, why don't we just rearrange psql to dynamically load a library of functions, eg: libpsql72.so libpsql73.so etc... And in them you have functions like: printTableDef(); printViewDef(); etc... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can we revisit the thought of PostgreSQL 7.2.4?
On Saturday 18 January 2003 00:08, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: Incidentally, has anyone else noticed the security update onslaught from Red Hat for older PostgreSQL versions? They even backported the fixes to 6.5.3 from Red Hat 6.2 (as well as for 7.0 and 7.1 as released in the respective Red Hat Linux versions). Should I forward that notice here? Some of the guys in Toronto got excited about it, but I can't see a lot of value there myself. If you're still running 6.5.3, is it likely you notice updates from anywhere? Why not? Upgrading to another major version of most things isn't supported by Red Hat within a particular version. KDE is a prime example. GNOME is another. XFree86 is another. BIND is yet another, although BIND8 upgrades are available for the systems that shipped with BIND4. RPM itself is one of the few exceptions. Going to 7.3 from 6.5 is not an update. And lots of sites are still running 6.2. IIRC Red Hat's up2date automatic upgrader tool was available in 6.2, and I know other autoupdaters are available. And, as we all know, automatic upgrade of PostgreSQL is only possible within a major version. Plenty of security conscious people still run Red Hat 6.2. Many probably pay for the enterprise support contracts through Red Hat, costing much money. Hmmmph, I know of a user running a couple of sites still running 5.2, with no intention of upgrading those machines. Will PostgreSQL 7.3 even build on Red Hat Linux 5.2? Forced upgrades are nonsense. So I'm glad Red Hat decided to put resources into supporting their userbase (even given the sunset on said support). On the BSD front, OpenBSD in particular still is running ancient versions of some core network stuff, due to the extreme security nature of that OS. Last I looked at OBSD it still shipped BIND4. 4.9 something. Positively ancient code that they have thoroughly audited. BIND8 hadn't at that time been fully audited. Red Hat 6.2 is still nominally supported (until March 31, it says here) so I suppose there's a corporate compulsion to back-patch anything that's labeled a security issue. But let's get real ... PG 6.anything is stone-age code now. Why? If a user doesn't need the features of 7.x.x, and the codebase is working well for him/her, why should said user/DBA feel compelled to go through who knows what mechanations to upgrade to the latest version? That's Microsoft-think. The upgrade from a 6.5.3 system to a 7.3.1 system is likely to be traumatic at least and cataclysmic at worst (to upgrade PostgreSQL may require upgrading the whole OS, which may require more memory (maybe more memory than the motherboard will support, even)). Yes, let's get real -- not everybody needs or necessarily even wants all the improved features of PG 7.3 versus even 6.5. The 'corporate compulsion' you mentioned is more widely known as 'customer service.' IOW, you want to stay in business, you support your customers. The Red Hat 5.2 user mentioned previously is perfectly happy with the featureset of PostgreSQL 6.3.2 (which is what 5.2 shipped with) and won't upgrade until it's very necessary. But this is a very low resource machine, where even the Linux 2.0 kernel makes sense. Now 6.5.3 will build on 5.2, but I haven't tried anything more recent. And 6.3.2 is enough database for their uses -- but these machines are in roles where security issues could be problematic. If it were easier to upgrade, they might consider it. _Of_course_ I'm not advocating that _we_ support these old of systems (after all, the PostgreSQL Global Development Group _has_ no customers) -- but it _is_ nice when a distributor acknowledges their older customers with real security updates within their released versions, and doesn't force major upgrades when unnecessary. Now if the user needs _features_ then the upgrade is justified, and I have no sympathy for a user who wants, say, schema support backpatched to 7.0.3, for instance. That request is just ridiculous. But for security and critical bugfixes, it should not be a forced major version upgrade, unless the bugfix cannot be easily backported. I for one intend to get the source RPM's for the fixed packages -- who knows, maybe some of the patches include the ability to rebuild on later Red Hat Linux versions, helping my upgradability crusade a little. As to the 7.2.4 issue, much if not all of our userbase is more than used to multiple concurrent OS kernel branches. Linux users in particular are very used to parallel versions -- the 2.0.x and 2.2.x series still get occassional releases even with 2.4.x out, and the development versions are in parallel constantly (except during the first few versions of a recent stable) with stable releases.. FreeBSD has their branches, etc. I think we should release a 7.2.4 if the bugfixes warrant it. (Not a 7.1.4, though, or 7.0.4, or 6.5.4, or 6.4.3, or 6.3.3,
Re: [HACKERS] Generate user/group sysids from a sequence?
Bruce Momjian [EMAIL PROTECTED] writes: OK. Do we have many people left upgrading from pg_dump's that COPY into pg_shadow? Hm, good point. I had forgotten we ever did that ;-) It looks like 7.0.* was the last release where pg_dumpall did that. Is that far enough back? [ looks further... ] Actually, such a dump is broken now anyway, because the column layout of pg_shadow has changed since 7.0. So I think it's a moot point. We could perhaps arrange the code so that if nextval'ing the sequence produces a duplicate sysid, we just loop back and nextval again until we get a nonconflicting id. I had hoped to remove the seqscan of pg_shadow from CREATE USER; but we could replace it with syscache probes for duplicate usename and id, and just repeat the syscache probe if we have to do another nextval. This way, we don't need to bother with touching the sequence at all during a CREATE USER with explicit sysid. 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] Win32 port
Jan is working on the port and should be posting it to the patches list in the next few days. After that, we will all look over the patch, port it from 7.2.1 to CVS HEAD, and make improvements before applying to CVS HEAD. Stay subscribed to hackers and you will see all the activity as soon as it starts. Also, Patrick is working on reviewing PITR so that is moving ahead as well. --- Viacheslav N Tararin wrote: Hi. Where I can download sources of win32 port? Can I help for win32 port? Thanks. ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Generate user/group sysids from a sequence?
On Fri, Jan 17, 2003 at 11:38:24AM -0500, Bruce Momjian wrote: Tom Lane wrote: A small difficulty is that explicitly-specified sysids could conflict with sysids generated later by the sequence. We could perhaps fix this by forcing up the sequence setting to be at least as large as an explicitly-given ID (compare the handling of explicitly loaded OIDs). A sequence sounds like a good idea. When we create a user, we can use MAX() to find the maximum, and if that is less than the sequence value, bump up the sequence to equal max and add the row, again incrementing the sequence. Another idea would be to put a trigger on the column so that any INSERT/UPDATE would automatically bump up the sequence with setval(). Hmm, unlike the OID case, I'd think there's unlikely to be many 'preused' userids. Why not just retry if the sequence hits an existing entry? Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Generate user/group sysids from a sequence?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK. Do we have many people left upgrading from pg_dump's that COPY into pg_shadow? Hm, good point. I had forgotten we ever did that ;-) It looks like 7.0.* was the last release where pg_dumpall did that. Is that far enough back? [ looks further... ] Actually, such a dump is broken now anyway, because the column layout of pg_shadow has changed since 7.0. So I think it's a moot point. Good. We could perhaps arrange the code so that if nextval'ing the sequence produces a duplicate sysid, we just loop back and nextval again until we get a nonconflicting id. I had hoped to remove the seqscan of pg_shadow from CREATE USER; but we could replace it with syscache probes for duplicate usename and id, and just repeat the syscache probe if we have to do another nextval. This way, we don't need to bother with touching the sequence at all during a CREATE USER with explicit sysid. Well, the problem is that this could still cause the reuse of a deleted user, no? Wasn't that the problem we were originally trying to solve? The reason I was suggesting the trigger/setval earlier is that it would eliminate the sequential scan and prevent reuse, I think. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Generate user/group sysids from a sequence?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: This way, we don't need to bother with touching the sequence at all during a CREATE USER with explicit sysid. Well, the problem is that this could still cause the reuse of a deleted user, no? Wasn't that the problem we were originally trying to solve? Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID setting to the sequence, and bump up the sequence if it's greater. Annoying, but I guess there's no choice. Yea, I wished we could have avoided it too, but at least we are removing the sequential scan. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Generate user/group sysids from a sequence?
On Fri, 2003-01-17 at 12:36, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: This way, we don't need to bother with touching the sequence at all during a CREATE USER with explicit sysid. Well, the problem is that this could still cause the reuse of a deleted user, no? Wasn't that the problem we were originally trying to solve? Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID setting to the sequence, and bump up the sequence if it's greater. Annoying, but I guess there's no choice. *puts on 'outside the box' hat* I'm sure most of you are familiar with at least one flavor of Unix, so I'm sure I'm not going to leave anyone confused. (I hope.) Why not maintain a sequence, but allow implicit UID assignment? ie; CREATE USER user --UID1; CREATE USER user2 WITH UID 2; --UID2 DROP USER user2 --buhbye; CREATE user2 --Now gets UID3; CREATE user3 WITH UID2 --user3 now has UID2; Default behaviour being if a UID has -been- used (not is -in- use) that it continues on? -Ketrien Saihr-Kenchedra I don't need no steeenking sig! http://ljpg.sf.net/ - http://www.blurty.com/~ketrien/ ---(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] Generate user/group sysids from a sequence?
Have we decided it's really too difficult to remove all references to a given sysid when the user is dropped? It seems like we're creating multiple new problems in an effort to workaround one existing problem. Robert Treat On Fri, 2003-01-17 at 12:38, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: This way, we don't need to bother with touching the sequence at all during a CREATE USER with explicit sysid. Well, the problem is that this could still cause the reuse of a deleted user, no? Wasn't that the problem we were originally trying to solve? Hmm, yeah I guess so. Okay, we do need to compare an explicit SYSID setting to the sequence, and bump up the sequence if it's greater. Annoying, but I guess there's no choice. Yea, I wished we could have avoided it too, but at least we are removing the sequential scan. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Generate user/group sysids from a sequence?
Robert Treat [EMAIL PROTECTED] writes: Have we decided it's really too difficult to remove all references to a given sysid when the user is dropped? Getting at objects in other databases is considerably less practical than anything we've discussed here. At a minimum I think it would require launching an additional backend to connect into each other database. But that is pretty ugly because then the updates in each database are distinct transactions --- what if one fails, and you've already committed removals in other databases? In an ideal world we'd drop a user's objects during DROP USER, but I don't foresee achieving that nirvana any time soon. At least not unless we want to change users to become local to databases. 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] Generate user/group sysids from a sequence?
On Fri, 2003-01-17 at 14:32, Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: Have we decided it's really too difficult to remove all references to a given sysid when the user is dropped? Getting at objects in other databases is considerably less practical than anything we've discussed here. At a minimum I think it would require launching an additional backend to connect into each other database. But that is pretty ugly because then the updates in each database are distinct transactions --- what if one fails, and you've already committed removals in other databases? Perhaps you don't allow the removal unless all databases came up clean. You can have the db admin go in a clean up each database manually if need be. Granted this doesn't solve getting at those other databases in the first place, but it eliminates any transactional issues. Robert Treat ---(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] Generate user/group sysids from a sequence?
Robert Treat [EMAIL PROTECTED] writes: On Fri, 2003-01-17 at 14:32, Tom Lane wrote: Getting at objects in other databases is considerably less practical than anything we've discussed here. Perhaps you don't allow the removal unless all databases came up clean. You can have the db admin go in a clean up each database manually if need be. Granted this doesn't solve getting at those other databases in the first place, but it eliminates any transactional issues. Oh? What if someone creates an object belonging to the user after we've finished examining that database? (Actually, this issue exists even within a single database; so I suppose we'd need to invent some kind of lock on userids ...) 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] Win32 port (native)
My idea was to go through the patch and break it out into the items it addresses: fork/exec loop rename test handle \r in COPY copydir for cp -r backslash tests rmdir not recursive for rm -r shared memory could map to new address in exec child compatibility defines file path separators root directory rename atomicity spinlock changes str[r]chr timeval for psql DWORD in help.c initdb etc. Once it is split out, each piece can be analyzed to make sure we are doing things the right way, then applied in parts. --- Jan Wieck wrote: Okay, I have finally extracted out a patch that applied to a 7.2.1 tree get's me something that compiles and passes all regression tests on RedHat Linux and Windows 2000. To clearify upfront, even if the build process of this port uses a few cygwin tools, the final executables and libraries do not depend on cygwin. I need to write up the 42 steps how to build. For those who want to be prepared, you'll need a unix system to patch, a win32 environment with VC++ 6.0, Tcl, bison*, flex*, sed* and touch* (*=from cygwin). To run the regression tests, one would need an MKS toolkit as it crashes the cygwin bash ... we didn't bother with those minor issues. I will post 2 patches later (meaning, probably tomorrow or latest Sunday) to the patches list. One will be all the changes to existing files (about 11,500 lines context diff), one will be the new files added. As a PostgreSQL coreteam member I want to thank my employer, the PeerDirect Corporation, for contributing this work, which IMHO is an important step for PostgreSQL. What we need from here are some ideas how this port can be lifted up to the current 7.4 development tree. There are some TODO items scattered throughout the code. But, it compiles and works, so it's a good point to start from I think. Looking at the demand for a native Win32 port I would expect some people willing to take it from there. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] IPv6 patch
Bruce Momjian writes: OK, you mentioned you want to put IPv6 addresses in pg_hba.conf even if the OS doesn't support it. How do others feel about that. We do leave the local in there even if the OS doesn't support it. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] IPv6 patch
Peter Eisentraut wrote: Bruce Momjian writes: OK, you mentioned you want to put IPv6 addresses in pg_hba.conf even if the OS doesn't support it. How do others feel about that. We do leave the local in there even if the OS doesn't support it. Good point. I will have the IPv6 be in there all the time. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Can we revisit the thought of PostgreSQL 7.2.4?
Tom Lane wrote: Red Hat 6.2 is still nominally supported (until March 31, it says here) so I suppose there's a corporate compulsion to back-patch anything that's labeled a security issue. But let's get real ... PG 6.anything is stone-age code now. regards, tom lane Red Hat Database project PS: I'm not taking a position on Justin's suggestion that there should be a 7.2.4. Marc and Bruce would be the ones who have to do the work, so they get to make the decision... Who, us? Well, there is the confusion factor of releasing a patch to a superceeded major version. Wrapping it up and putting it out really isn't a big deal. Marc? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
