Re: [SQL] Diferent databases on same query...
On Saturday 20 October 2001 08:53, you wrote: > Andre, > > The only reason to have data for one query in different databases is if > the databases are on different machines. If you're running on PC-type > hardware, you might have to do that some times for performance reasons. > But if at that point, you should ask yourself if you shouldn't invest in > some faster hardware ;-). There are many more good reasons to do so. Example from my own domain, medicine: 1.) one database hold demographic information. This information is needed by administration, nursing, and medical staff as well as by external service providers such as pathology. 2.) another database holds medical information. The information here is highly sensitive. The database admin should be a medical doctor,and it must be unaccessible to clerical staff. SQL permissions are only a *very* weak protection, not good enough for this purpose. For many good reasons, foremost security, this information will not reside in the same database (sometimes not even on the same machine) as the administrative data or the demographic data. 3.) For obvious reasons, it would be bad to replicate the demographic data in order to provide this data to administration, medical staff etc for their own purposes. Thus, we have multiple databases on multiple servers all depending on each other. Some servers are read-only, others need the backend to log every single query for audit trailing. The latter alone would blow the storage needs out of proportion if applied to the whole set of databases. At present, it is up to our client software to sort this out "manually". A minor headache. Horst ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] serial data type
CREATE TABLE tablename (colname SERIAL);
okay, but how do I set the beginning number. So far the only thing I
have been able to do is directly after creating the serial column, I do:
select setval('tablename_colname_seq', 15753);
Is this the only way to set the beginning number of a sequence?
Thanks for all your help guys,
Ted Petrosky
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Connecting to different DataBase In PlPgsql Function
Bhuvan, > How can we connect to different database using plpgsql function? Can > we? No, you can't. -Josh ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Diferent databases on same query...
Horst, > There are many more good reasons to do so. Example from my own > domain, > medicine: > Thus, we have multiple databases on multiple servers all depending on > each > other. Your reasons all make sense. What you need is not inter-database queries but a robust middleware layer, such as J2EE. Your sort of situation is why middleware exists. If GreatBridge was still around, I'd reccommend you contact them, as I knwo they implemented a solution for your sort of situation. However, I don't know who'd do it now. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] GUID in postgres
Hi I think most of you are missing the point here. GUIDs are unique and they are required for Enterprise development. For those of you who have used multi master replication on Oracle or MS SQL, you know that identity columns and sequences are problematic. The only way to work with primary key columns in to seed the values of the identity column or sequence differently for each of the servers in the cluster, such that, a duplicate value would never occur. This is a very sloppy approach to dealing with the problem as it requires a considerable amount of maintenance; furthermore, anyone who takes this approach shouldn't be working with computers. In this type of environment you would use a GUID in place of an identity column. Since the value is unique on the individual server and on the cluster of servers (because the MAC addresses are different) you won't be getting errors from non-unique values being inserted in the primary key during the replication. We've have one system that has been running MS SQL for over a year now without any GUID errors. The largest table in the system has over 19 million records. Do a search for "multi master replication" and you should pick up some info on how to properly implement a GUID. The simplest approach is to use some combination of a sequence and the MAC address. > Hi, > Is there any concept of sys_Guid in postgres. > If yes what is that ?? > As i am using GUID in sql and oracle, then what is counterpart of this in postgres. It is urgent. > > Thanks in advance > Bye > Dinesh Parikh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] GUID in postgres
On ðÔÎ, 2001-10-26 at 21:14, [EMAIL PROTECTED] wrote: > Hi > > I think most of you are missing the point here. > I'm missing nothing :) "uniqueidentifier" (128-bit unique value) data type fot PostgresSQL exists and is used at least by me. It utilizes functionality of uuid library from widely used in Linux e2fsprogs package. Uuid library itself can generate UUIDs with MAC/time but if /dev/urandom was detected the true random 16 bytes will be used by default. And yes, this type is great for primary keys. Regards, Dmitry ---(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: [SQL] GUID in postgres
Folks, > I think most of you are missing the point here. > > GUIDs are unique and they are required for Enterprise development. I don't know. I have yet to hear a persuasive argument as to why none of the schemes previously mentioned would not work. While a "GUID" automated by the RDBMS platform is convenient, it is by no means necessary. Any number of approaches can be substituted. For example, I have a system that requires middleware-level interaction between 3 database servers. The main data tables in these databases all have 2-column primary keys; one column for the (locally unique) sequence, one column for the (globally unique) server ID. This scheme fulfills all of the functionality that you describe, without the overhead of complex random seed mechanisms or other proprietary overhead. Also, any requests that are strictly local in nature need only query the local id without worrying about the second column. > Do a search for "multi master replication" and you should pick up > some > info on how to properly implement a GUID. The simplest approach is to > use some combination of a sequence and the MAC address. SO what happens if you swap out the network card? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Diferent databases on same query...
On Fri, 26 Oct 2001, Josh Berkus wrote:
> Horst,
>
> > There are many more good reasons to do so. Example from my own
> > domain,
> > medicine:
> > Thus, we have multiple databases on multiple servers all depending on
> > each
> > other.
>
> Your reasons all make sense. What you need is not inter-database
> queries but a robust middleware layer, such as J2EE. Your sort of
> situation is why middleware exists.
Josh,
I'm pretty sure Horst does this already - check out GNU Med.
(Hallo Horst, Viele Grüsse aus New York. ;-)
I guess the problem is that the procedural language mentioned in
the original post has a few hurdles yet to manage.
BTW, I do this (within the context of very small scripts) using
perl DBI. It is cake with Perl. And - your code is relatively
portable. Aside from certain Oracle idiosyncracies of course
(NVL, DECODE, SYSDATE...)
> If GreatBridge was still around, I'd reccommend you contact them, as I
> knwo they implemented a solution for your sort of situation. However, I
> don't know who'd do it now.
What about RedHat? Are they working on this?
Dieter (Simader) of SQL Ledger also does this sort of thing within
his product (which uses perl dbi): www.sql-ledger.org
Cheers
Saint Vincent Catholic Medical Centers
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-818-5528
Behavioral Health Services Fax:718-818-5056
Residential ServicesMobile: 917-282-7359
/* Rekordmeister ist nur der FC Bayern München! */
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] GUID in postgres
Hi The overhead in generating GUIDs is minimal. I've never heard of a GUID implementation that uses random numbers. In most cases it is a functions of the date, a sequence, and/or the MAC. Why would you want to use two columns when you use could one? Two columns in each table would clutter your scheme as well as your procedures. It doesn't matter if you change the network card. The GUIDs will still be unique. The MAC does not make the GUID unique on any given server. The rest of the GUID generation function accomplishes this task. All that is accomplished by incorporating the MAC into the GUID is uniqueness between machines. -Original Message- From: Josh Berkus [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 11:31 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] GUID in postgres Folks, > I think most of you are missing the point here. > > GUIDs are unique and they are required for Enterprise development. I don't know. I have yet to hear a persuasive argument as to why none of the schemes previously mentioned would not work. While a "GUID" automated by the RDBMS platform is convenient, it is by no means necessary. Any number of approaches can be substituted. For example, I have a system that requires middleware-level interaction between 3 database servers. The main data tables in these databases all have 2-column primary keys; one column for the (locally unique) sequence, one column for the (globally unique) server ID. This scheme fulfills all of the functionality that you describe, without the overhead of complex random seed mechanisms or other proprietary overhead. Also, any requests that are strictly local in nature need only query the local id without worrying about the second column. > Do a search for "multi master replication" and you should pick up some > info on how to properly implement a GUID. The simplest approach is to > use some combination of a sequence and the MAC address. SO what happens if you swap out the network card? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] GUID in postgres
"Kaiserdigital": > Why would you want to use two columns when you use could one? Two > columns in each table would clutter your scheme as well as your > procedures. It's a relational integrity issue. A GUID, by definition, contains two pieces of information: 1. The local primary key 2. The server unique ID As such, RDBMS design principles (the Second Normal Form, I believe, correct me if I'm remebering wrong) mandates that they be kept in two columns. This is a pet peeve of mine, as DB vendors and beginner DBA's today seem to be in a rush to embrase "non-atomic" fields willy-nilly, abandoning 20 years of accumulated RDBMS wisdom. > It doesn't matter if you change the network card. The GUIDs will > still > be unique. The MAC does not make the GUID unique on any given server. > The rest of the GUID generation function accomplishes this task. All > that is accomplished by incorporating the MAC into the GUID is > uniqueness between machines. Makes sense if you have an open-ended network of machines so that a simple numbering sequence won't work. With 3 servers, "1", "2", "3" work just as well, and don't have the 12-byte overhead of a MAC address. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] rollback
> Hi everybody, > I was playing with psql and accidently deleted a couple of records from > my database. I am wondering if there is any way to restore them. I know > that in Oracle you can do 'rollback work' from SQLPlus interface and it > would rollback all the updates done to the database. That's available in PostgreSQL too. But, by entering psql autocommit is on by default, meaning every sql command is committed at once. To enable a rollback in psql you have to enter a transaction explicitly by using BEGIN; Everything done below BEGIN can be rolled back. Try. > I am pretty sure that from now on I would try to revoke permissions to > delete anything from the database from users like myself. What is the > best way to do this? Refer to the sql commands GRANT and REVOKE within the documentation. Additionally, you should check the CREATE GROUP command. Creating groups of users is a very elegant way to grant/revoke permissions on objects. As far as I know there is no way to revoke delete permissions on the database as whole, you have to list all tables you want to have delete permissions dropped. Regards, Christoph ---(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: [SQL] GUID in postgres
On óÒÄ, 2001-10-24 at 09:55, Dinesh Parikh wrote: > Hi, > Is there any concept of sys_Guid in postgres. > If yes what is that ?? > As i am using GUID in sql and oracle, then what is counterpart of this in postgres. >It is urgent. > > Thanks in advance > Bye > Dinesh Parikh > Look at http://www.taurussoft.org/files/uniqueidentifier-0.1.9.tar.gz It uses libuuid from e2fsprogs to deal with UUIDs (GUIDs). Regards, Dmitry ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
